I have added a column to a table that will store a formatted string based on concatenating a number of other columns so I can search over it more easily. In order to save loading the whole table into another app and updating the new column then persisting, I want to write an UPDATE SQL query. But I can't figure out how to take an integer column and format it as in this example
Source column value = 1 Destination column value = 0001
I want the 3 leading zeros to be added in SQL.
Any ideas?
Here's how to prepend seroes:
right('0000' + cast(MyColumn, nvarchar(4), 4)
This will concatenate 0000 with whatever value there is in MyColumn and return four rightmost characters.
Try this:
SELECT RIGHT('000'+ CONVERT(VARCHAR,Source),4) AS ColWithZeros
FROM Table
select Right( '0000' + cast( 1 as varchar),4)
If it is coming from an integer column, I would remove the dependency on the string "0001".
Also, most of the other answers assume that 1 is the only data, I would suggest you need to find the number of digits, then add the appropriate number of zeroes. As it will fail with any aother data (10,20, 100 etc).
