This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Padding Numbers Using Transact-SQL

Numeric codes held in a database as integer values or other numeric types will sometimes need to be represented visually in different formats. A common request is to pad a short number with leading zeroes to achieve a specific string length.

Number Padding

Padding numbers is a common task when formatting numeric values for display. Usually leading zeroes are added to the left of a value to achieve the desired length of string. For example, adding zeroes to the integer value '1' to provide the formatted output string of '00001'. Unfortunately Transact-SQL (T-SQL) does not provide a function to pad a number with a specific character. Instead, two standard functions must be combined.

The Str function is used to convert numeric data to character data. The function accepts either two or three parameters. The first parameter is the number to be converted. The second parameter is the total length of the converted string and the optional third parameter specified the number of decimal places to convert.

If the numeric value is shorter than the desired length, spaces are added to the start of the value to effectively right-align it within the returned char. If the numeric value is too large for the desired length of string, the final char value will contain only a series of asterisks (*).

The Replace function can be used to identify the location all instances of one string expression within another and replace each occurrence with a new string. The three parameters for the function are the value to be searched, the string to search for and the replacement value. This function can be used in conjunction with Str to replace padding spaces with the zero character. For example:

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 '*****'
6 February 2008