Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Ridiculous String parsing

Posted on 2005-05-06
5
Medium Priority
?
210 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:cdfllc
  • 3
5 Comments
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13947821
is this  
6465 Waygata Blvd #404
St. Louis Park, MN 55426

in one Column  ?
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13947863
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
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 2000 total points
ID: 13947877
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
 
LVL 13

Expert Comment

by:softplus
ID: 13947897
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
 
LVL 1

Author Comment

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

thanks everyone!

cdfllc
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question