Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Parse City, State, Zipcode

Posted on 2013-01-31
3
Medium Priority
?
338 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 27

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 27

Accepted Solution

by:
Shaun Kline earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview
Suggested Courses
Course of the Month10 days, 18 hours left to enroll

885 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