SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 remove the string in front of street
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gongxia649
So Suave

Azores
344 Posts

Posted - 08/07/2006 :  10:15:32  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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

Edited by - gongxia649 on 08/07/2006 10:16:05

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 08/07/2006 :  10:18:51  Show Profile  Reply with Quote
how to determine where is the street name in the string ? Is it always removing the first word ?


KH

Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 08/07/2006 :  10:19:42  Show Profile  Reply with Quote
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!
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/07/2006 :  10:21:12  Show Profile  Visit gongxia649's Homepage  Reply with Quote
always removing number + letter from the street name.
1A street avenue

Edited by - gongxia649 on 08/07/2006 10:22:39
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/07/2006 :  10:23:22  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 08/07/2006 :  10:24:28  Show Profile  Reply with Quote
Will there be cases that it does not contain the number + letter ? just the street name ?


KH

Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 08/07/2006 :  10:28:07  Show Profile  Reply with Quote
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!
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/07/2006 :  10:32:00  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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



Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 08/07/2006 :  10:34:19  Show Profile  Reply with Quote
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!
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 08/07/2006 :  10:36:30  Show Profile  Reply with Quote
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!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 08/07/2006 :  10:44:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000