CODEWITHSUNDEEP
sqlsql-server
Luka
Luka

Reputation: 189

SQL Server : add characters to a column

I have problem with getting SQL code: in a column I have a value with 5 or 6 characters before that values I need put character :0 and value in column must have 7 characters. 

Column
------
123456           
123456
12345
12345
123456

This is my code which does not work (I am using SQL Server):

Update table
set column = CONCAT( '0',  ( column ) )
where LEN( + RTRIM ( column ) )  < 7

Update table
set column = CONCAT( '0', RTRIM ( column ) )
where LEN( RTRIM( column ) ) < 7

UPDATE table
SET column = '0' + column
WHERE LEN(column) = 7

My result : after my attempt, I get 0 before values but somewhere still 0 missing.

Column
-------
0123456           
0123456
012345
012345
0123456

I need :

Column
-------
0123456           
0123456
0012345
0012345
0123456

Thanks for updating my code

Upvotes: 4

Views: 13068

Answers (2)

LunarSage
LunarSage

Reputation: 285

From your examples, I am unsure whether there are multiple values in the same field. However, assuming the value for a given row must contain a single 7 digit number with 0 padding, see the examples below. Remember that the columns that stores the padded values must be a string such as VARCHAR.

UPDATE table SET column = RIGHT('0000000' + column, 7)

This will update any value to be padded with up to 7 zeros.

If your column is currently stored as an Integer rather than a String, use the following:

UPDATE table SET TextColumn = RIGHT('0000000' + CONVERT(VARCHAR, IntColumn), 7)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You want to left pad the value. Here is one method:

Update table
    set column = RIGHT(CONCAT( REPLICATE('0', 7),  ( column ) ), 7)
    where LEN( + RTRIM ( column ) )  < 7;

Upvotes: 5

Related Questions