Help on one colum

Hi,

I have a column in SQL that needs to be created. This column needs to start with 10 zero's. If I add a number, say 135 the column needs to read : 0000000135.

Is this possible? If it is how do I need to go about to do this. If I need to create a view or something like that I do not mind. I am open to all suggestions and appreciate the help.

Use the following query..

Select Replicate('0',10-len(Convert(Varchar,ColumnName))) + Convert(Varchar,ColumnName) From TableName

|||

DECLARE @.AnyStringNo NVARCHAR(10)
SET @.AnyStringNo = '135'
SELECT REPLICATE(0,10-LEN(@.AnyStringNo)) + @.AnyStringNo
SET @.AnyStringNo = '47593'
SELECT REPLICATE(0,10-LEN(@.AnyStringNo)) + @.AnyStringNo

Will give ever output in 10 digits with replicating of 0s.

|||

Thanks Mani,

This is exactly what I need.

|||

Thanks Bhudev,

This was also very helpful!!

|||

One more question Mani,

If you have a currency field with the following : 1.35 it imports the . as well. What do I need to do to get the . out of the query?

|||This one should remove the dot and pad the necessary 0s in front.

SELECT RIGHT('0000000000' + REPLACE(CONVERT(VARCHAR(10),col1),'.', '') , 10) FROM yourTable|||

Thanks Limno,

That is exactly what I wanted!

|||You should do the formatting on the client-side. It is much easier, simpler and flexible. So return the ID as integer to the client and let it to do the formatting as required. This also provides better performance since you will be sending less data from the server to the client(s) over the network.|||

Umachandar,

Forgive me, but I am kinda new to VB programming. How do I get the client side to do the formatting?