Cool Tips‎ > ‎String Manipulation‎ > ‎

String Manipulation - 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)


ċ
udf_AddLeadingZerosToInteger.sql
(1k)
Andy Hughes,
Jul 18, 2012, 12:38 PM
Comments