String formatting in T-SQL

Question

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?

Answer 1

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.

Answer 2

Try this:

SELECT RIGHT('000'+ CONVERT(VARCHAR,Source),4) AS ColWithZeros
FROM Table

Answer 3

select Right( '0000' + cast( 1 as varchar),4)

Answer 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).