Parse City State Zip

Posted on 2005-05-02
Last Modified: 2006-11-18
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.

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.
Question by:c0ry98
    LVL 14

    Expert Comment

    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

    Author Comment

    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.  

      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;


    Panama City Beach
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Just make sure the system feeding this app does not allow the zip+4 to come in as" 12345 6789"

    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Oracle sql question 9 58
    PL/SQL Group by 2 34
    oracle 10G 5 37
    Getting network error using utl tcp package 7 60
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now