SQL Server Concatenate string column value to 5 char long

Scenario:

I have a table1(col1 char(5)); A value in table1 may '001' or '01' or '1'.

Requirement:

Whatever value in col1, I need to retrive 开发者_C百科it in 5 char length concatenate with leading '0' to make it 5 char long.

Technique I applied:

select right(('00000' + col1),5) from table1; 

I didn't see any reason, why it doesn't work? but it didn't. Can anyone help me, how I can achieve the desired result?

Since you're using a fixed width column, it's already of size 5 (with whitespace). You need to trim it:

DECLARE @table1 TABLE (col1 char(5))

INSERT INTO @table1 (col1) VALUES ('12345')
INSERT INTO @table1 (col1) VALUES ('1')

SELECT RIGHT('00000'+RTRIM(col1),5) FROM @table1

-- Output:
-- 12345
-- 00001

Or use varchar instead:

DECLARE @table2 TABLE (col1 varchar(5))

INSERT INTO @table2 (col1) VALUES ('12345')
INSERT INTO @table2 (col1) VALUES ('1')

SELECT RIGHT('00000'+col1,5) FROM @table2

-- Output:
-- 12345
-- 00001

If you are storing the data in a CHAR field you are probably getting right spaces buffered with blanks. e.g. 01 = "01 ". If your do a RIGHT("00000" + value, 5) it'll still be the original value. You need to do a RTRIM() on the value or store the data in a VARCHAR field.

The problem is that the char(5) field is always 5 characters long, not matter what you put into it. If you insert '01' into the field, the value stored is actually '01   ' (note the trailing spaces).

Try this:

select right(('00000' + replace(col1, ' ', '')), 5)

Edit: I will leave my answer here as an example, but Michael's answer using rtrim is better.

you need to store your data in a consistent manner, so you don't need to write queries to format the data each time. this will fix your existing data:

UPDATE table1
    SET col1= RIGHT('00000'+ISNULL(RTRIM(col1),''),5)

now every time you select you only have to do this:

SELECT col1 FROM table1

however, you must make sure that the data is formatted properly (leading zeros) every time it is inserted. I'd add a check constraint just to make sure:

ALTER TABLE table1 ADD CONSTRAINT
    CK_table1_col1 CHECK (LEN(col1)=5)

and when you insert do this:

INSERT INTO table1
        (col1, ...
    VALUES
        (RIGHT('00000'+ISNULL(RTRIM(@col1),''),5)