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
LVL 1
cdfllcAsked:
Who is Participating?
 
Melih SARICAOwnerCommented:
tested and here is the result

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

select substring(@addr,1,charindex(char(13),@addr,1)-1) adress1,
     substring(@addr,charindex(char(13),@addr,1)+1,charindex(',',@addr,1)-1) city
     rtrim(substring(@addr,charindex(',',@addr,1)+2,2)) state ,
     ltrim(right(@addr,(len(@addr)-(charindex(',',@addr,1)+3)))) zip
0
 
Melih SARICAOwnerCommented:
is this  
6465 Waygata Blvd #404
St. Louis Park, MN 55426

in one Column  ?
0
 
Melih SARICAOwnerCommented:
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
0
 
softplusCommented:
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
0
 
cdfllcAuthor Commented:
you are right softplus, I am just converting an existing batch of addresses...
Not hoping for a perfect result...

thanks everyone!

cdfllc
0
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.

All Courses

From novice to tech pro — start learning today.