Padding Numbers Leading Zeros Using Transact-SQL
30-Dec-0909 Leave a comment
From
Use Str function which converts numeric data to a string.
SELECT replace(str(1,5),’ ‘,’0’) — Outputs ‘00001’
SELECT replace(str(123,5),’ ‘,’0’) — Outputs ‘00123’
SELECT replace(str(12345,5),’ ‘,’0’) — Outputs ‘12345’
SELECT replace(str(123456,5),’ ‘,’0’) — Outputs ‘*****’
SELECT replace(str(123,5),’ ‘,’0’) — Outputs ‘00123’
SELECT replace(str(12345,5),’ ‘,’0’) — Outputs ‘12345’
SELECT replace(str(123456,5),’ ‘,’0’) — Outputs ‘*****’
there is a third argument to str function which is no of decimals to keep
Stephen Shakespeare 