Add Leading Zeros to String


SQL Server 2000: Not Supported**

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested


Author: ChillyDBA

Date: 18 Jul 2012


One of the string modifications that I regularly come across is the need to add leading zeros to numbers for presentation/labelling purposes

eg When dynamically constructing a file label to include the date, I usually want to include leading zeros on the day and month portions to enable the files to be sorted and/or vsially easy to search - 3 Jan 2012 becomes 20120103

This is relatively straightforward to do, but the code can become repetitive, and several times I've fallen foul of implicit conversion 'features' with inline string manipulation i.e. the output string generally adopts the data type of the input string unless specifically cast - this can cause problems if the input string data types vary.

Utilizing a function to left-pad the string circumvents most of these problems.

** The code uses VARCHAR(MAX) so is marked as unsuitable for SQL 2000. A quick modification to VARCHAR(8000) would make it work.



DROP FUNCTION dbo.udf_AddLeadingZerosToInteger


CREATE FUNCTION dbo.udf_AddLeadingZerosToInteger


@String VARCHAR(MAX), -- the integer to add leading zeros to - as a string

@StringLength INT -- the final length of the left-padded string





Purpose: Left-pads an integer with 0s.

The integer is passed as a string.

The final string length is passed in also

Output is a string of the string length

e.g @String = '12'

@StringLength = 6

@Result = '000012'

Author: ChillyDBA

History 18 Jul 2012 - Initial Issue




SELECT @ResultString = RIGHT(REPLICATE('0' ,@StringLength) + @String, @StringLength)

RETURN @ResultString


Test Code:

SELECT dbo.udf_AddLeadingZerosToInteger('456', 7)

SELECT dbo.udf_AddLeadingZerosToInteger(CAST(123 AS VARCHAR(MAX)), 9)