extract State Entity from String using regex_substr

Question

Let's say I have the following column of city, state, and zip values

╔═══════════════════════════════════╗
║ CITY_STATE_ZIP_COLUMN             ║
╠═══════════════════════════════════╣
║ ARLINGTON WASHINGTON 98223        ║
║ MONTGOMERY VILLAGE MARYLAND 20886 ║
║ BROOKLYN NEW YORK 11233-0000      ║
║ CHARLESTON MISSISSIPPI 38921-0000 ║
║ AUGUSTA GEORGIA 30909             ║
║ CLINTON SOUTH CAROLINA 29325-0000 ║
╚═══════════════════════════════════╝

I'm trying to figure out how to write a oracle SQL statement to extract just the state.

I can figure out how extract the ZIP using the following:

SELECT REGEXP_SUBSTR(CITY_STATE_ZIP_COLUMN,'(\d)(\d)(\d)(\d)(\d)')
FROM My_Table

HOWEVER, For the life of me, I can't figure out how I can use REGEXP_SUBSTR to get ONLY the state.

Is there a more efficient way to get the state perhaps using a combination of SUBSTR and INSTR functions?

Answer 1

You could create a table with a list of state names, and use that to cross reference against the list.

Something like:

SELECT s.state_name
FROM states s
    INNER JOIN addresses a ON a.address LIKE '% ' || s.state_name || ' %';

Since you cannot create tables, you could use a CTE with defined values for each state name, something like:

WITH states (state_name) AS
(
    SELECT 'Alabama' FROM dual
    UNION ALL SELECT 'Georgia' FROM dual
    UNION ALL SELECT 'Alaska' FROM dual
    ...    
)
SELECT s.state_name
FROM states s
    INNER JOIN addresses a ON a.address LIKE '% ' || s.state_name || ' %';

---

^{I'm not an Oracle rockstar; who knew || was concatenation}

Answer 2

Using the above column name as the example above, and knowing I am only pulling US states and territories, I came up with the following case statement using a combination of substring and instring functions in oracle. It works for what I need:

,CASE when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'YORK'
             THEN 'NEW YORK'
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'JERSEY'
             THEN 'NEW JERSEY'
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'HAMPSHIRE'
             THEN 'NEW HAMPSHIRE'        
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'MEXICO'
             THEN 'NEW MEXICO'
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'RICO'
             THEN 'PUERTO RICO'
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'ISLAND'
             THEN 'RHODE ISLAND'                
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'SAMOA'
             THEN 'AMERICAN SAMOA'
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,3)+1),
             (instr(city_state_zip_column,' ',-1,2)-instr(city_state_zip_column,' ',-1,3))-1) = 'NORTH'
             AND 
            substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'CAROLINA'
             THEN 'NORTH CAROLINA' 
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,3)+1),
             (instr(city_state_zip_column,' ',-1,2)-instr(city_state_zip_column,' ',-1,3))-1) = 'SOUTH'
             AND 
            substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'CAROLINA'
             THEN 'SOUTH CAROLINA'
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,3)+1),
             (instr(city_state_zip_column,' ',-1,2)-instr(city_state_zip_column,' ',-1,3))-1) = 'NORTH'
             AND 
            substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'DAKOTA'
             THEN 'NORTH DAKORA' 
      when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,3)+1),
             (instr(city_state_zip_column,' ',-1,2)-instr(city_state_zip_column,' ',-1,3))-1) = 'SOUTH'
             AND 
            substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'DAKOTA'
             THEN 'SOUTH DAKOTA'
       when substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,3)+1),
             (instr(city_state_zip_column,' ',-1,2)-instr(city_state_zip_column,' ',-1,3))-1) = 'WEST'
             AND 
            substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) = 'VIRGINIA'
             THEN 'WEST VIRGINIA'
       else
             substr(city_state_zip_column,(instr(city_state_zip_column,' ',-1,2)+1),
             (instr(city_state_zip_column,' ',-1,1)-instr(city_state_zip_column,' ',-1,2))-1) 
             END AS STATE