Add Leading Zeros to String

Applicability:

SQL Server 2000: Not Supported**

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 18 Jul 2012

Description

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.

Code

Function:

DROP FUNCTION dbo.udf_AddLeadingZerosToInteger

GO

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

)

RETURNS VARCHAR(MAX)

AS

/**********************************************************

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

***********************************************************/

BEGIN

DECLARE @ResultString VARCHAR(MAX)

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

RETURN @ResultString

END

Test Code:

SELECT dbo.udf_AddLeadingZerosToInteger('456', 7)

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