October 6, 2004 at 2:20 pm
Hi there (orginally I put this post at the beginner forum, but perhaps it was the wrong place)
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
I really need to solve this problem, so any help is very much appreciated. Thanks in advance.
Joejoe
October 6, 2004 at 2:32 pm
you can use various means using
CASE ...look it up in online help
RIGHT( '000' + RTRIM( numbercolumn ) , 3 )
and/or
PATINDEX( '%[A-Z]%, numbercolumn)
(THAT IS, I don't know what possibilities you have for alphas after the number so you'll have to account for this in your update SET)
October 6, 2004 at 3:56 pm
How about something like this:
select
CASE PATINDEX( '%[a-Z]%', col1)
WHEN 0 THEN
stuff('000',4-len(col1),len(col1),col1)
ELSE
stuff('000',4-PATINDEX( '%[a-Z]%', col1)+1,len(col1),col1)
END
from test1
Where test1 is the table in question and col1 is the column you are working on.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply