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
 dts parsing street name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 08/01/2006 :  11:23:43  Show Profile  Visit funketekun's Homepage  Reply with Quote
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
30282 Posts

Posted - 08/01/2006 :  11:53:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There is no wasy way, I think.

You will have to do a lot of replacing.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 08/01/2006 :  13:25:28  Show Profile  Visit funketekun's Homepage  Reply with Quote
replace could be one way. anyone else has a better way?
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 08/02/2006 :  10:23:28  Show Profile  Visit funketekun's Homepage  Reply with Quote
anyone?
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/02/2006 :  11:33:07  Show Profile  Reply with Quote
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.
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/02/2006 :  22:44:17  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 08/02/2006 :  23:07:55  Show Profile  Reply with Quote
gongxia649,

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


KH

Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/03/2006 :  09:10:16  Show Profile  Reply with Quote
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.
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 08/03/2006 :  10:50:12  Show Profile  Visit funketekun's Homepage  Reply with Quote
the code works only for that address. i have 1000000 addresses.
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 08/03/2006 :  10:53:34  Show Profile  Visit funketekun's Homepage  Reply with Quote
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/03/2006 :  11:03:41  Show Profile  Reply with Quote
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.
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