Link to home
Start Free TrialLog in
Avatar of holemania
holemania

asked on

SQL Parse City, State, Zipcode

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
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of holemania
holemania

ASKER

Awesome.  Thank you.