• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1104
  • Last Modified:

Parse City State Zip

Hi everyone,

I have the some data that I need to parse from an existing Oracle table.  One of the columns has City State and Zip in a single column and I'd like to split those into separate columns.  The format is one of the following: City, State Zip; City, State Zip+4; City State Zip; or City State Zip+4.

Here are a couple records from the table that should give represent all the possible formats listed above.

CITY_STATE_ZIP
------------------------------------
Red Springs NC 28377
Panama City Beach FL 32411
Manchester NH 03103-4209
Cantonment, FL 32533-6848

I'm looking for a select statement that will parse this data into three separate columns.
0
c0ry98
Asked:
c0ry98
1 Solution
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
I don't think you can get this easily. As the there should be some specific criteria for splitting these into three columns. Currently
Red may be a city, Red Springs may be a city even Red Springs NC may be a city.
there should be some comma seperated or '-' included values which splits them into as required.

Let's see if some other can do it. With only these piece of information it is rather difficult, i think
0
 
c0ry98Author Commented:
I think I got it finally!  It goes backwards and picks up the first space as the end of zip, the next space as state, and everything from the first character to the state is then picked up as city.  



select
  substr(patient_city_state_zip, 1, (instr(patient_city_state_zip,' ', -1, 2))) as City,
  substr(patient_city_state_zip, (instr(patient_city_state_zip,' ', -1, 2)), 3) as state,
  substr(patient_city_state_zip, (instr(patient_city_state_zip,' ', -1, 1)), length(patient_city_state_zip)) as "zip"
FROM test;

CITY
-----------------
STA
---
zip
-----------------
Manchester
 NH
 03103-4209


CITY
---------------------
STA
---
zip
---------------------
Panama City Beach
 FL
 32411
0
 
slightwv (䄆 Netminder) Commented:
Just make sure the system feeding this app does not allow the zip+4 to come in as" 12345 6789"
0
 
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now