Solved

SQL Parse City, State, Zipcode

Posted on 2013-01-31
3
332 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot Table for a join with partition by and over clause 7 36
SQL Sub-Query Help 22 62
Need help on t-sql 2012 10 53
SSRS 2013 - Creating a summarized report 19 32
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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…

785 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