Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Parse City, State, Zipcode

Posted on 2013-01-31
3
333 Views
Last Modified: 2013-01-31
I have a field that contains all these fields into one field.  How can I parse it in SQL?

City, State, Zipcode

Example:

Omaha, NE 68176
Shawnee Mission, KS 66225
Dallas, TX 75220-2514

Output:
City                        State                 Zipcode
Omaha                    NE                       68176
Shawnee Mission     KS                       66225
Dallas                       TX                        75220-2514
0
Comment
Question by:holemania
  • 2
3 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 38841237
If you are guaranteed to find the comma in the field, you can use the CHARINDEX function to find the position of the comma and use the LEFT function to get the text upto the comma. The SUBSTRING function would allow you to get the state using the position of the comma + 1 and a string length of 2 characters. The zip code can be retrieved using the RIGHT function along with the REVERSE and CHARINDEX functions for finding the position of the last space.
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 38841268
Here's an example:
DECLARE @Test varchar(50)
SET @Test = 'Omaha, NE 68176'

SELECT LEFT(@Test, CHARINDEX(',', @Test) - 1),
    SUBSTRING(@Test, CHARINDEX(',', @Test) + 2, 2),
    RIGHT(@Test, CHARINDEX(' ', REVERSE(@Test)) - 1)
0
 

Author Comment

by:holemania
ID: 38841295
Awesome.  Thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

791 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