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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome. Thank you.