Extract State Entity from String using regex_substr

oracle

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?

Best Answer

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

Related Question