Thursday, November 6, 2008

How do I pad digits with leading zeros?

How do I pad digits with leading zeros?

Here is one way. Basically, it compares the number of desired digits with the length of the number being padded, and creates that many instances of zero to its left.

<%
' Pad Digits with leading Zeros.
' Basically, it compares the number of desired digits with the length of the number being padded, and creates that many instances of zero to its left.
Function PadDigits(Num, totalDigits)
If Len( Num ) > totalDigits Then
'PadDigits = Num
else
PadDigits = Right(String(totalDigits,"0") & Num, totalDigits)
End if
End Function
%>

And in JavaScript:



Sample usage:

<% Response.Write(PadDigits(46,8)) ' returns 00000046 Response.Write(PadDigits(4,5)) ' returns 00004 Response.Write(PadDigits(32,6)) ' returns 000032 Response.Write(PadDigits(22,1)) ' returns 22 %>

And here are a couple of methods in T-SQL:

SELECT REPLACE(STR(column, 5), SPACE(1), '0') FROM Table

SELECT RIGHT('00000' + RTRIM(column), 5) FROM Table

And here is a technique that can be useful for sorting on a varchar column that might contain numbers (you can't order by CAST AS INT because some values will contain characters):

SELECT column FROM table ORDER BY RIGHT('00000' + RTRIM(column), 5)

To see how this can change a query, consider the following example (SQL Server):

SET NOCOUNT ON
CREATE TABLE #foo(bar VARCHAR(5))
INSERT #foo VALUES('1')
INSERT #foo VALUES('2')
INSERT #foo VALUES('9')
INSERT #foo VALUES('10')
INSERT #foo VALUES('20')

-- compare the results of this query:
SELECT bar FROM #foo ORDER BY bar

-- to the results of this query:
SELECT bar FROM #foo ORDER BY RIGHT('00000' + bar, 5)

DROP TABLE #foo


No comments: