SQL Server Concatenate row column value up to 5 char long

Question

Scenario:

I have table1 (col1 char (5)); The value in table 1 may be "001" or "01" or "1".

Demand:

Regardless of the value in col1, I need to extract it into a 5 char length concatenate with a leading “0” to make it length

Used technique:

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

I did not see the reasons why this does not work? but this is not so. Can someone help me how can I achieve the desired result?

Answer 1

Since you are using a fixed-width column, it already has a size of 5 (with a space). 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 store the data in the CHAR field, you are likely to get the correct spaces filled with spaces. for example 01 = "01". If you make the LAW (value "00000" +, 5), it will still be original. You need to do RTRIM () for the value or save the data in the VARCHAR field.

Answer 3

The problem is that the char (5) field always has a length of 5 characters, no matter what you put in it. If you insert '01' into the field, the value is actually stored as '01 '; (pay attention to trailing spaces).

Try the following:

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

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

Answer 4

you need to save your data in a consistent way, so you do not need to write queries to format the data every time. this will fix your existing data:

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

Now every time you choose, you only need to do this:

 SELECT col1 FROM table1 

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

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

and when you insert this:

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