SQL Tips by Namwar Rizvi

August 24, 2007

Number padding in TSQL

Filed under: string concatenation,string manipulation,tips,TSQL — namwar @ 9:24 PM

Sometime we need to pad leading zeros to numeric
values so that they can appear having same width
like 00001,0022,0934 etc.
Following code demonstrates the technique
to pad leading zeros in front of digits.

–Variable to hold max length
Declare @m_maxLength int

–Create a test table
Declare @m_testTable table (sampleValue int)

–Insert some sample values
Insert into @m_testTable values (1)
Insert into @m_testTable values (2)
Insert into @m_testTable values (19)
Insert into @m_testTable values (201)
Insert into @m_testTable values (20231)

–Calcualte the length of maximum number in the table
Select
@m_maxLength=Max(Len(convert(varchar,sampleValue)))
from @m_testTable

—Now select the values
Select
sampleValue,
Replicate(‘0’,@m_maxLengthLen(convert(varchar,sampleValue)))
+
convert(varchar,sampleValue) as paddedValue

from @m_testTable

Advertisements

5 Comments »

  1. This worked great, if you need leading zeros for say 10 characters, but your max is actually 6, you can always just manually set the max to 10! I did that in my case and it worked great.

    Declare @m_maxLength int
    Select
    @m_maxLength=8
    from Unit

    update Unit
    set EndUserNumber = Replicate(‘0’,@m_maxLength-Len(convert(varchar,EndUserNumber)))
    +convert(varchar,EndUserNumber)

    Comment by Travis Riffle — October 5, 2009 @ 2:19 PM | Reply

  2. you can try this

    Replace(STR(sampleValue, 6), ‘ ‘, ‘0’)

    Comment by Muhammad Nawaz — June 28, 2010 @ 2:35 PM | Reply

  3. This worked. i used t to automatically generate value for a column in a stored procedure

    ‘D’ + Replicate(‘0’,4 – len(trip.TotalDrivers() + 1)) + Cast(trip.TotalDrivers() + 1 as nvarchar(4))

    Comment by Adesupo Adeyeye — March 24, 2011 @ 3:40 PM | Reply

  4. Is there a way to create this into a function? Seems that it would be handy to reuse for other projects.

    Comment by Tim @ Paper Shredder Reviews — July 20, 2011 @ 11:30 PM | Reply

  5. Very nice post. I certainly appreciate this website. dbakeeegddkd

    Comment by Johne751 — May 12, 2014 @ 11:08 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: