remove the string in front of street

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

remove the string in front of street

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-07 : 10:15:32

street_name
---------------------
1A HAYES ST
11a yONU STREET

i need to parse off watever is in front of the street_name. anyone
has any approach>?

street_name
-------------
HAYES ST
yonu street

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 10:18:51

how to determine where is the street name in the string ? Is it always removing the first word ?

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-07 : 10:19:42

the problem is defining the begining of the street name, imho. You could use the position of the 1st space in the data you provided, but then what to you do if you get:


1 A Hayes St

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

gongxia649
So Suave

344 Posts

Posted - 2006-08-07 : 10:21:12

always removing number + letter from the street name.
1A street avenue

gongxia649
So Suave

344 Posts

Posted - 2006-08-07 : 10:23:22

quote:
Originally posted by Wanderer

the problem is defining the begining of the street name, imho. You could use the position of the 1st space in the data you provided, but then what to you do if you get:

1 A Hayes St

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

there wont be like 1 A hayes. i worry about this later.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 10:24:28

Will there be cases that it does not contain the number + letter ? just the street name ?

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-07 : 10:28:07

sample code for your data, using place of the space to substring:


SET NOCOUNT ON 
DECLARE @Streets TABLE (StreetName varchar(100))
INSERT INTO @Streets (StreetName)
			Select '1a Hayes st'
UNION ALL	SELECT '11a y0nu Street'
SELECT substring(StreetName,(charindex(' ',StreetName))+1,len(StreetName)-(charindex(' ',StreetName))) AS FinalProduct FROM @Streets

results:


FinalProduct
----------------------------------------------------------------------------------------------------
Hayes st
y0nu Street

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

gongxia649
So Suave

344 Posts

Posted - 2006-08-07 : 10:32:00

quote:
Originally posted by khtan

Will there be cases that it does not contain the number + letter ? just the street name ?

KH

no case like that. i already took care of that.

im developing this to get rid of '1A'

update address set street_name = case
when left(ltrim(street_name), 2) like '[1-9][a-z]'
and substring(street_name, 1, 3) in ('')
then street_name ...i dont know

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-07 : 10:34:19

As Khtan is implying - the problem with my code is that if you get given:


Hayes Street

as input, it will follow it's rules (select data after the 1st space), and output:


Street

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-07 : 10:36:30

quote:
Originally posted by gongxia649

quote:
Originally posted by khtan

Will there be cases that it does not contain the number + letter ? just the street name ?

KH

no case like that. i already took care of that.

im developing this to get rid of '1A'

update address set street_name = case
when left(ltrim(street_name), 2) like '[1-9][a-z]'
and substring(street_name, 1, 3) in ('')
then street_name ...i dont know

how about:


update address 
set street_name = substring(street_name,(charindex(' ',street_name))+1,len(street_name)-(charindex(' ',street_name)))
when left(ltrim(street_name), 2) like '[1-9][a-z]'
--and substring(street_name, 1, 3) in ('')

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-07 : 10:44:22

If you use front end application, then you can use split function there and omit index 0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources