Additional 0 to column data

Hi

I have a column in the db with serial number data, on a export that I am doing the data has to be 10 digits long the problem is not all of them are eg

12234

122334343

1234234567

how can i get it to look like this adding an 0 to the front to make the row 10 digits

0000012234

0122334343

1234234567

Thanks

SELECT REPLICATE('0',10 - LEN(CONVERT(VARCHAR(10),Column1))) + CONVERT(VARCHAR(10),Column1)

HTH,

Babu

|||

select right('000000000' + convert(varchar(10),ColumnName),10)

example

declare @.i int
select @.i = 12345

select right('000000000' + convert(varchar(10),@.i),10)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Alternatively perform the formatting in your front end application|||

If the number is to be stored as a number, then this is the best advice. I wouldn't suggest it if this is a value that will be used many different places and never be used in a math equation. For that I would store it in a character string and prepend the zeros.

The real problem comes in all of the different places it is used (reports, data warehouse,etc.) Someone has to format it, and you don't want the user to have to use some UI function to format it. You might do the formatting on the way to the DW and to a reporting data store, but to me it begs the question of the nature of the data. If the nature of the data is a code that happens to be all numbers (but would perform just as well in the application if it was not all numbers, ie 'asd02020' would not change the application as opposed to '000022020', then store it as a character, format it when you save it, and get it over with :)