| Author |
 Topic  |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
 Posted - 08/01/2006 : 11:23:43
|
hi, im running into a bit of a problem. i will like to string parse the street name into what you see below using DTS. How can i get around this?
12 north plaza boulevard apt.16
12|N|plaza blvd| apt16
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/01/2006 : 11:53:05
|
There is no wasy way, I think.
You will have to do a lot of replacing.
Peter Larsson
Helsingborg, Sweden |
 |
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 08/01/2006 : 13:25:28
|
| replace could be one way. anyone else has a better way? |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 08/02/2006 : 10:23:28
|
| anyone? |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 08/02/2006 : 11:33:07
|
Rotsa-ruck developing an all-encompassing algorithm to parse street names. It must be possible, because google maps appears to do it, but there are so many possible variations that you are looking at an extremely long and buggy script.
If you ever do develop such a thing, or find one on the net, please post it for the rest of us. |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/02/2006 : 22:44:17
|
12 north plaza boulevard apt.16
i want to split the address and put it into multiple fields.
address_number
12
suffixA
North
street_name
plaza blvd
suffixb
apt16
anyone can help?
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 08/03/2006 : 09:10:16
|
quote:
Originally posted by gongxia649
12 north plaza boulevard apt.16
i want to split the address and put it into multiple fields.
address_number
12
suffixA
North
street_name
plaza blvd
suffixb
apt16
anyone can help?
Sure. Use this:declare @GongxiaAddress char(31)
set @GongxiaAddress = '12 north plaza boulevard apt.16'
select left(@GongxiaAddress, 2) as address_number,
substring(@GongxiaAddress, 4, 5) as suffixA,
substring(@GongxiaAddress, 10, 15) as street_name,
right(@GongxiaAddress, 5) as suffixB
I'd put it in a function if I were you, so that you can re-use it. |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 08/03/2006 : 10:50:12
|
| the code works only for that address. i have 1000000 addresses. |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 08/03/2006 : 10:53:34
|
i wrote this code to take care of the suffix in the right side. i dont know if there is a better way to write this code.
update #manipulatedAddress set full_address = ltrim(rtrim(full_address))
update address set postDirection = case
when right(rtrim(full_address), 2) like '#%'
and substring(reverse(full_address), 3, 1) in ('', ',', '.')
then right(full_address, 2)
when right(rtrim(full_address), 3) like '#%%'
and substring(reverse(full_address), 4, 1) in ('', ',', '.')
then right(full_address, 3)
when right(rtrim(full_address), 4) like '#%%%'
and substring(reverse(full_address), 5, 1) in ('', ',', '.')
then right(full_address, 4)
when right(rtrim(full_address),7) like 'unit #%'
and substring(reverse(full_address), 8,1) in ('', ',', '.')
then right(full_address,7)
when right(rtrim(full_address),8) like 'unit #%%'
and substring(reverse(full_address), 9,1) in ('', ',', '.')
then right(full_address,8)
when right(rtrim(full_address),9) like 'unit #%%%'
and substring(reverse(full_address), 10,1) in ('', ',', '.')
then right(full_address,9)
when right(rtrim(full_address),6) like 'unit %'
and substring(reverse(full_address), 7,1) in ('', ',', '.')
then right(full_address,6)
when right(rtrim(full_address),7) like 'unit %%'
and substring(reverse(full_address), 8,1) in ('', ',', '.')
then right(full_address,7)
when right(rtrim(full_address),8) like 'unit %%%'
and substring(reverse(full_address), 9,1) in ('', ',', '.')
then right(full_address,9)
when right(rtrim(full_address),5) like 'apt %'
and substring(reverse(full_address), 6,1) in ('', ',', '.')
then right(full_address,5)
when right(rtrim(full_address),6) like 'apt %%'
and substring(reverse(full_address), 7,1) in ('', ',', '.')
then right(full_address,6)
when right(rtrim(full_address),7) like 'apt %%%'
and substring(reverse(full_address), 8,1) in ('', ',', '.')
then right(full_address,7)
when right(rtrim(full_address),3) in ('apt')
and substring(reverse(full_address), 4,1) in ('', ',', '.')
then right(full_address,3)
when right(rtrim(full_address),4) like 'apt%'
and substring(reverse(full_address), 5,1) in ('', ',', '.')
then right(full_address,4)
when right(rtrim(full_address),5) like 'apt%%'
and substring(reverse(full_address), 6,1) in ('', ',', '.')
then right(full_address,5)
when right(rtrim(full_address),6) like 'apt%%%'
and substring(reverse(full_address), 7,1) in ('', ',', '.')
then right(full_address,6)
when right(rtrim(full_address),5) like 'lot %'
and substring(reverse(full_address), 6,1) in ('', ',', '.')
then right(full_address,5)
when right(rtrim(full_address),6) like 'lot %%'
and substring(reverse(full_address), 7,1) in ('', ',', '.')
then right(full_address,6)
when right(rtrim(full_address),7) like 'lot %%%'
and substring(reverse(full_address), 8,1) in ('', ',', '.')
then right(full_address,7)
else null
end |
Edited by - funketekun on 08/03/2006 10:54:25 |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 08/03/2006 : 11:03:41
|
quote:
Originally posted by funketekun
the code works only for that address. i have 1000000 addresses.
You will need 1,000,000 functions. But don't worry, SQL Server can handle up to 2,147,483,647 different objects, so you are well within the server's limitations. |
|
|
| |
Topic |
|
|
|
dts parsing street name
