Leading Zeros for Integer

[aumstu]

I am inserting a number into a database and I want the number to display as 003250...but in the database it is just 3250. Is there anyway to make this appear correctly without changing the data type to varchar or something else?

Thanks

 

[SQLDenis]

pick your poison

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


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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> pick your poison



------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[GShen]

RIGHT(FIELDNAME + 10000000,7)

Remember when... everything worked and there was a reason for it?

 

[aumstu]

Thanks SQLDenis....I have to export the DB to a text file and I will do the conversion there...thanks for the info

 

[aumstu]

If this DB will be exported to a text file...then if I am thinking right, it will not matter if my data type is an integer or a varchar b/c the text file will have to be imported into their system.

Would anybody disagree with the above statement?

thanks and thanks for responding

 

[SimonSellick]

If this is really a number, then it should be imported as such. If it is a code that happens to be made up of digits, then it should be a text field, constrained by whatever method you want to use. Numbers are easy to spot by usage: do you need to do arithmetic with it? If it is a number, then you can use the formatting rules suggested above to get it to look the way you want on output. If it's really a code, then it's probably better to store the formatted value in a text field and come up with some rules for generating the codes.

HTH

 

[SQLDenis]

Even if you format it with leading zeros, those will be dropped after they import this file (if the field is int of course)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

Thanks for inputs...I am sure that some arithmetic we be done on some of the fields but that will be on their end...I will leave it varchar and let them import it the way they need to...

thanks again