Every once in a while I need something like this, and I always forget the best way to do it: pad numbers so they start with leading zeros so they appear fixed with.
There is no ready made function in SQL Server which offers this solution but we can quickly write up something very simple.
SQLUSA has some more ways to do it, and all basically come down to this:
- Convert your number to VARCHAR (use CAST or CONVERT) .
- Prepend the converted number with the maximum number of zeros you require (either with a fixed length string like
'00000000', or use the REPLICATE function). - Take the right most characters of the required length using the RIGHT function.
You can do similar things with LEFT and padding (for instance with spaces to the right).
One example:
SELECT RIGHT('0000000000' + CAST(31415 AS VARCHAR(10)), 10) AS PaddedPiInteger
SELECT LEFT(CAST(31415 AS VARCHAR(10)) + ' ', 10) AS PaddedPiInteger
–jeroen
The Wiert Corner - irregular stream of stuff 