Parsing address parts from one string in MS SQL

I have been attempting to adjust this code I found to meet my situation. I have an export table where the address was put into one field as opposed to four. The structure is

MyCity,MyState MyZipCode MyCountry -  there is only one comma and then 2 spaces

I was trying to use parsename and get it to convert the string to
MyCity.MyState.MyZipCode.MyCountry

I cannot get the script right because I can only get it to insert as period after MyCity and the value of ParseLen is always zero. Is it possible to adjust this code to accomplish what I need or is there another way?

select 
replace(parsename(CitySTZip,ParseLen +1),',','') as Address1, 
replace(parsename(CitySTZip,ParseLen ),',','') as Address2, 
replace(parsename(CitySTZip,ParseLen -1),',','') as Address3, 
replace(parsename(CitySTZip,ParseLen -2),',','') as Address4 
from( 
select case  ascii(right(rtrim(CitySTZip),1)) when 44 then 
replace(replace(left(CitySTZip,(len(CitySTZip)-1)),',','.'),char(20),'.')   
else  
replace(replace(CitySTZip,',','.'),char(10),'.') end as CitySTZip, 
case  ascii(right(rtrim(CitySTZip),1)) when 44 then 
len(replace(replace(CitySTZip,',','.'),char(20),'.')) - len(replace(replace(CitySTZip,',','.'),char(10),'')) -1 
else 
len(replace(replace(CitySTZip,',','.'),char(20),'.')) - len(replace(replace(CitySTZip,',','.'),char(10),'')) end as ParseLen 
 from VendorsNTI) x

Open in new window

LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
try like this.
select *,REPLACE(REPLACE(CitySTZip,' ','.'),',','.') NewAddressColumn from VendorsNTI

Open in new window

check this example
declare @table table (CitySTZip nvarchar(100))
insert @table values ('MyCity,MyState MyZipCode MyCountry')
select *,REPLACE(REPLACE(CitySTZip,' ','.'),',','.') NewCitySTZip from @table
/*CitySTZip	    MyCity,MyState MyZipCode MyCountry
NewCitySTZip	MyCity.MyState.MyZipCode.MyCountry
*/

Open in new window

0
SharathData EngineerCommented:
If you want the data in seperate strings, try like this.
select *,PARSENAME(REPLACE(REPLACE(CitySTZip,' ','.'),',','.'),1) Address1,
       PARSENAME(REPLACE(REPLACE(CitySTZip,' ','.'),',','.'),2) Address2,
       PARSENAME(REPLACE(REPLACE(CitySTZip,' ','.'),',','.'),3) Address3,
       PARSENAME(REPLACE(REPLACE(CitySTZip,' ','.'),',','.'),4) Address4 
  from VendorsNTI

Open in new window

check this example.
declare @table table (CitySTZip nvarchar(100))
insert @table values ('MyCity,MyState MyZipCode MyCountry')
select *,PARSENAME(REPLACE(REPLACE(CitySTZip,' ','.'),',','.'),1) Address1,
       PARSENAME(REPLACE(REPLACE(CitySTZip,' ','.'),',','.'),2) Address2,
       PARSENAME(REPLACE(REPLACE(CitySTZip,' ','.'),',','.'),3) Address3,
       PARSENAME(REPLACE(REPLACE(CitySTZip,' ','.'),',','.'),4) Address4 
  from @table
/*
CitySTZip	Address1	Address2	Address3	Address4
MyCity,MyState MyZipCode MyCountry	MyCountry	MyZipCode	MyState	MyCity
*/

Open in new window

0
Scott PletcherSenior DBACommented:
The City and State are easy, as long as you base it solely on the comma, and not spaces (e.g. "Salt Lake City, Utah").

    LEFT(CitySTZip, CHARINDEX(',', CitySTZip) - 1) AS City,
    LEFT(LTRIM(SUBSTRING(CitySTZip, CHARINDEX(',', CitySTZip) + 1, 1000)),
        CHARINDEX(' ', LTRIM(SUBSTRING(CitySTZip, CHARINDEX(',', CitySTZip) + 1, 1000)))) AS State,    

The last two are a pain, because the country could also have extra space(s).

Will the zipcode always end in a digit?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

rwheeler23Author Commented:
These addresses are for both USA and Canada. It appears Canada zipcode also always end in a digit. Now sometimes there is no zip code at all. We are only talking 2000 vendor addresses here so they will need to mop up those that do not convert correctly.

The city name may have spaces but the country is either USA, Canada or blank.
0
rwheeler23Author Commented:
I had to add a check that any value was specified at all otherwise the script would error. The country is always last.

City, State  ZipCode  Country is the way most of these are presented.
select case when CHARINDEX(',', CitySTZip) > 0 then
		 LEFT(CitySTZip, CHARINDEX(',', CitySTZip) - 1)  else ' ' end AS City,
		 LEFT(LTRIM(SUBSTRING(CitySTZip, CHARINDEX(',', CitySTZip) + 1, 1000)), 
		 CHARINDEX(' ', LTRIM(SUBSTRING(CitySTZip, CHARINDEX(',', CitySTZip) + 1, 1000)))) AS State
  from VendorsNTI

Open in new window

0
SharathData EngineerCommented:
Did you try my query? Can you give some more examples where it is breaking?
0
rwheeler23Author Commented:
The first does not do much of anything. The second replaces all commas and space with periods.
I have attached the import file so you can see the variations of addresses. I had used this text file and imported the data into SQL.
Vendors.txt
0
Scott PletcherSenior DBACommented:
>> The second replaces all commas and space with periods. <<

Again, you cannot replace all spaces with periods and the parse on period -- because of city names like "Salt Lake City".

0
Scott PletcherSenior DBACommented:
With those extras, the code will need to be more sophisticated.  You really need a table-value function and you can CROSS APPLY to it.
0
rwheeler23Author Commented:
That is what I beginning to wonder. If they are willing to pay for it I will get to work on it. Thanks.
0
rwheeler23Author Commented:
Thanlks for you help. Mopping up bad data is always an interesting experience.
0
Scott PletcherSenior DBACommented:
Since the zip will always end in a digit, and no (?) cities that I knnow of have a digit in the name, you can find the zip by using PATINDEX('%[0-9]%', CitySTZip), and use that to help parse out the last two values.
0
rwheeler23Author Commented:
That help but there are some Canadian addresses in there and those zip codes end in a letter. I was able to process about 90% without any user intervention.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.