# Simple life, Complicated mind

## Thursday, November 6, 2008

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 ifEnd Function%>

And in JavaScript:

Sample usage:

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

 SELECT REPLACE(STR(column, 5), SPACE(1), '0') FROM TableSELECT 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 ONCREATE 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