dts parsing street name - SQL Server Forums

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

dts parsing street name

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-08-01 : 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

30421 Posts

Posted - 2006-08-01 : 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

491 Posts

Posted - 2006-08-01 : 13:25:28

replace could be one way. anyone else has a better way?

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-08-02 : 10:23:28

anyone?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-02 : 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

344 Posts

Posted - 2006-08-02 : 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)

17689 Posts

Posted - 2006-08-02 : 23:07:55

gongxia649,

you can take a look at the GETWORDNUM found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51289

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-03 : 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

491 Posts

Posted - 2006-08-03 : 10:50:12

the code works only for that address. i have 1000000 addresses.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-08-03 : 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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-03 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources