asked on

Ridiculous String parsing

I know this is probably impossible, but does anybody have an idea how to convert a column with contents like:

6465 Waygata Blvd #404
St. Louis Park, MN 55426

Into This:

Address1 City State Zip
---------- ---- ------ ----
6465 Waygata Blvd #404 St. Louis Park MN 55426

thanks for any suggestions,
cdfllc

Melih SARICA

is this
6465 Waygata Blvd #404
St. Louis Park, MN 55426

in one Column ?

Melih SARICA

declare @addr varchar(250)
set @addr='6465 Waygata Blvd #404
St. Louis Park, MN 55426
'

select substring(@addr,1,charindex(char(13),@addr,1)-1) adress,
substring(@addr,charindex(char(13),@addr,1)+1,charindex(',',@addr,1)-1) adress2,
rtrim(substring(@addr,charindex(',',@addr,1)+2,2)) state ,
ltrim(right(@addr,(len(@addr)-(charindex(',',@addr,1)+3)))) zip

ASKER CERTIFIED SOLUTION

Melih SARICA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

softplus

Are you sure you want to do this? What about different adress formats, commas in the city name, po-box numbers, etc? If you just need to convert an existing batch of adresses, this might be a good start -- but I wouldn't do it and hope for a perfect translation (it's not possible in a simple query..)
Just my 2eurocents :)
John

cdfllc

ASKER

you are right softplus, I am just converting an existing batch of addresses...
Not hoping for a perfect result...

thanks everyone!

cdfllc