I've got a table1(col1 char(5)) Something in table1 may '001' or '01' or '1'.


Whatever value in col1, I have to retrive it in five char length concatenate with leading '0' to really make it 5 char lengthy.

Technique I applied:

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

I did not use whatever reason, why it does not work? however it did not. Can anybody assist me to, the way i is capable of the preferred result?

Since you are utilizing a fixed width column, it's already of size 5 (with whitespace). You have 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 rather:

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're storing the information inside a CHAR area you're most likely getting right spaces buffered with blanks. e.g. 01 = "01 ". In case your perform a RIGHT("00000" + value, 5) it'll be the initial value. You have to perform a RTRIM() around the value or keep data inside a VARCHAR area.

However , the char(5) area is definitely 5 figures lengthy, not appear you place in it. Should you place '01' in to the area, the worthiness saved is really '01   ' (note the trailing spaces).

Do this:

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

Edit: I'll leave my answer here for example, but Michael's answer using rtrim is much better.

you have to store your computer data inside a consistent manner, so you don't have to write queries to format the information every time. this can fix your overall data:

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

now any time you choose you just do that:

SELECT col1 FROM table1

however, you have to make certain the information is formatted correctly (leading zeros) each time it's placed. I'd give a check constraint simply to make certain:

    CK_table1_col1 CHECK (LEN(col1)=5)

so when you place do that:

        (col1, ...