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
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).
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:
ALTER TABLE table1 ADD CONSTRAINT CK_table1_col1 CHECK (LEN(col1)=5)
so when you place do that:
INSERT INTO table1 (col1, ... VALUES (RIGHT('00000'+ISNULL(RTRIM(@col1),''),5)