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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply