Monday, March 25, 2013

Left Padding Numerics with Leading Zeros

Many times you may need to show numeric data with a fixed length. If you cast an int to a string datatype you often get a bunch of trailing spaces. What if you convert the int to a fixed length string with STR(), thereby right-aligning the int data, and then change the leading spaces to zero characters with REPLACE(). 

Check example to show the integer 24 as a six character fixed length string with leading zeros:

DECLARE @i int
SET @i = 24
SELECT REPLACE(STR(@i, 6, 0), ' ', '0')

No comments:

Post a Comment