| Author |
 Topic  |
|
|
gongxia649
So Suave
Azores
344 Posts |
 Posted - 08/07/2006 : 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 |
Edited by - gongxia649 on 08/07/2006 10:16:05
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 08/07/2006 : 10:18:51
|
how to determine where is the street name in the string ? Is it always removing the first word ?
KH
|
 |
|
|
Wanderer
Flowing Fount of Yak Knowledge
United Kingdom
1167 Posts |
Posted - 08/07/2006 : 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
Azores
344 Posts |
Posted - 08/07/2006 : 10:21:12
|
always removing number + letter from the street name.
1A street avenue
|
Edited by - gongxia649 on 08/07/2006 10:22:39 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/07/2006 : 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)
Singapore
16746 Posts |
Posted - 08/07/2006 : 10:24:28
|
Will there be cases that it does not contain the number + letter ? just the street name ?
KH
|
|
|
|
Wanderer
Flowing Fount of Yak Knowledge
United Kingdom
1167 Posts |
Posted - 08/07/2006 : 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
Azores
344 Posts |
Posted - 08/07/2006 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1167 Posts |
Posted - 08/07/2006 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1167 Posts |
Posted - 08/07/2006 : 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
India
22461 Posts |
Posted - 08/07/2006 : 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 |
|
|
| |
Topic |
|
remove the string in front of street
