SQL Server Concatenate string column value to 5 char long

Question

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 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?

Answer 1

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

Answer 2

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.