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