Monday, December 9, 2013

Zero padded numbers in MS SQL Server

The easiest way to pad a number with zeros is to use the str function. Count how many decimals you want, add the number of places you want in front of the number, then add one for the decimal point. That's the length, the last parameter is the number of places you want after the decimal point. That will pad the decimals with zeroes but the front with spaces so if you need zero padded numbers replace the spaces with zeroes.

select replace(str(235.367,12,6), ' ', '0') =   '00235.367000'