June 12, 2003 at 2:50 pm
Hello,
In a table, I have a varchar(100) field called 'StreetName' in a table called 'tblStreetData' in which the actual street name record can ocassionally be prefixed with 'N', 'S', 'E', 'W' in order to designate a point of direction. The prefixes are always followed by a space and then the street name.
I need to delete these prefixes and remove the space from such records. I just started working on this, and I think I need to use LTRIM with an UPDATE statement in some way.
Any ideas?
Thanks!
CSDunn
June 12, 2003 at 3:18 pm
This example uses 788, 567, and 577 instead of N,S,E,W. Replace 4 & -4 with 2 & -2, and 5 with 3.
USE pubs
SELECT stor_address,
CASE WHEN SUBSTRING(stor_address,0,4) IN('788','567','577') THEN SUBSTRING(stor_address,5,LEN(stor_address)-4) ELSE stor_address END
AS CutAddress
FROM stores
Everett
Everett Wilson
ewilson10@yahoo.com
June 12, 2003 at 3:20 pm
update tblStreetData
set streetName =
case when substring(a.streetName,1,2) = 'N ' then substring(a.streetName,3,len(a.streetName)-2)
when substring(a.streetName,1,2) = 'S ' then substring(a.streetName,3,len(a.streetName)-2)
when substring(a.streetName,1,2) = 'E ' then substring(a.streetName,3,len(a.streetName)-2)
when substring(a.streetName,1,2) = 'W ' then substring(a.streetName,3,len(a.streetName)-2)
else streetName
end
from tblStreetData a
BUT... if the table is huge:
I would avoid doing an update. I would try to do an insert into a new table. drop the old table and then rename the new table. Something like that.
insert into newTable ()
select
case when substring(a.streetName,1,2) = 'N ' then substring(a.streetName,3,len(a.streetName)-2)
when substring(a.streetName,1,2) = 'S ' then substring(a.streetName,3,len(a.streetName)-2)
when substring(a.streetName,1,2) = 'E ' then substring(a.streetName,3,len(a.streetName)-2)
when substring(a.streetName,1,2) = 'W ' then substring(a.streetName,3,len(a.streetName)-2)
else streetName
end as streetName,
[other fields here]
from tblStreetData a
drop table tblStreetData
???
master.dbo.sp_rename [dbName].dbo.newTable [dbName].dbo.tblStreetData
oops... I was beat to it.
Edited by - jraha on 06/12/2003 3:21:20 PM
June 12, 2003 at 3:27 pm
Thanks for your help!
CSDunn
June 12, 2003 at 6:08 pm
Noting that you say they only occasionally contain the prefixes, another option that will only issue updates against rows that fit the criteria is:
update tblStreetData
set StreetName = SUBSTRING(StreetName, 3, LEN(StreetName)-2)
where StreetName LIKE '[NEWS] %'
Cheers,
- Mark
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy