Tuesday, November 08, 2005

Pad Numbers By Using CASE, LEFT And CONVERT

Let's say you have a table with integer values below 100000 and you want them in the same format with leading zeros. For example 500 has to be 000500 and 1 has to be 000001
How do you do this in SQL server?
Below are two ways to accomplish this.

CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000

--Use CASE, LEN and CONVERT to pad the values
SELECT value,CASE LEN(value)
WHEN 1 THEN '00000' + CONVERT(VARCHAR,value)
WHEN 2 THEN '0000' + CONVERT(VARCHAR,value)
WHEN 3 THEN '000' + CONVERT(VARCHAR,value)
WHEN 4 THEN '00' + CONVERT(VARCHAR,value)
WHEN 5 THEN '0' + CONVERT(VARCHAR,value)
ELSE CONVERT(VARCHAR,value)
END AS Formattedvalue
FROM #ValueTable


--Use LEFT, LEN and CONVERT to pad the values
SELECT value,LEFT('000000',(6 -LEN(value )))
+ CONVERT(VARCHAR,value) AS Formattedvalue
FROM #ValueTable

I have received a comment from Rob Farley who was so kind to point out to me that I should have used RIGHT instead of left
Here is the code he supplied

--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS FormattedValue
FROM #ValueTable

3 comments:

Rob Farley said...

The way I would do this is:

select value, right('000000' + convert(varchar,value),6) as FormattedValue
from #ValueTable

I don't see the point in finding the length of the value and subtracting that from 6, and using left, when you can just use 'right'.

After all, both fail when it gets longer than six digits.

Rob Farley said...

I'm sorry I used the word 'lousy' in my post. That was a bit strong I know. Nice to find a Bergkamp fan - the man's a genius!

Anyway, keep it up!

Mandar said...

nice info

http://mandar-punaskar.blogspot.com