Update with zero

Question

Post reply

Update with zero

joe joe-152023

SSCommitted

Points: 1819
More actions
October 6, 2004 at 6:32 am

#162831

Hi there
How do I update a column with zeros in front?
I have a column (Char) with house numbers from 1 to 999 and now and then letters a to z.
Now it looks like this,
1
23A
454
56
But I want it to look like this,
001
023A
454
056
Thanks in advance
Joejoe

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antonio Eubanks SSC Journeyman Points: 86 More actions · Answer 1

Joejoe,

Try this statement if the values are in one column:

update YOURTABLE

set YOURCOLUMN='0'+YOURCOLUMN

or you can use this is you need to zeroes based upon the data

update YOURTABLE

set YOURCOLUMN=

case

when len(YOURCOLUMN)=? then '0'+YOURCOLUMN

when len(YOURCOLUMN)=? then '00'+YOURCOLUMN

else null

end

I wasn't sure why you added zeros to some values and not the others, however, the case statement should work for any conditions if "tweeked" a little.

Chris Scott SSC Veteran Points: 297 More actions · Answer 2

If yourcolumn is defined as char(4) then the following will work

update yourtable set yourcolumn =

case when isnumeric(yourcolumn) <> 0 then right(rtrim(stuff(yourcolumn,1,0,'000')),3) else right(rtrim(stuff(yourcolumn,1,0,'000')),4) end

Otherwise,if yourcolumn is defined as varchar(4), you do not need to use rtrim, ie following will work:

update yourtable set yourcolumn =

case when isnumeric(yourcolumn)<> 0 then right(stuff(yourcolumn,1,0,'000'),3) else right(stuff(yourcolumn,1,0,'000'),4) end

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

If your goal is to pad with zeroes to a fixed width, this works with no CASE statements:

set housenumber = right('0000' + ltrim(rtrim(housenumber)), 4)

This assumes your desired field width is 4, adjust as necessary.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

Ignore that last post, I reread the question and realize you want fixed-width numbers and the letters are extra. Go back to Chris' solution.

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 5

Thanks to all of you, it have been a big help to me that I could get the solution from you helpful people.

I have used Chris' solution with succes.