Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1094
  • 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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