Solved

SQL Parse City, State, Zipcode

Posted on 2013-01-31
3
336 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
[X]
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
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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