Padding out a field of numerics

[grayt26]

Hi,

I have a field of numeric values of different lengths, example

20
200
20000

I need these all to be padded out so that
00020
00200
02000

I thought that there was a command that could do this but i can not find it.

Thanks

 

[mutley1]

Gray,

George wrote a good response to your question under the following post.

http://www.tek-tips.com/viewthread.cfm?qid=1326299

HTH

M.

 

[mutley1]

Apologies,

SQLSister also had a good comment depending on your datatypes for the field in question.

 

[grayt26]

can you tell me why

Right('0000000000'+field,10)


does not work, it is not adding the zero's, it is just ignoring them.

I have also tried

cast(Right('0000000000'+hosp,10) as varchar(36))

still no zero's

 

[mutley1]

Are you selecting or trying to update the table? If it is an update, what is the datatype (see noted on other post about them being ignored if it is a numeric).

 

[grayt26]

I was doing a select, testing before i do an update. I have changed the field to varchar now and it works ok with the zero's but i don't understand why it never liked the cast i used.

 

[monksnake]

I have also tried

cast(Right('0000000000'+hosp,10) as varchar(36))

still no zero's

In this case you would need to cast BEFORE you try to add the 0000 to hosp

Right('0000000000' + convert(varchar(10), hosp), 10)

*tested


<.