Return to Question

improved formatting

Source Link

edited Aug 9, 2017 at 19:42

70.5k
22
177
321

hwo to extract State Entity from String using regex_substr

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

╔═══════════════════════════════════╗
║ 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_SUBSTRREGEXP_SUBSTR to get ONLY the state.

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

Some help would be very much appreciated.

hwo to extract State Entity from String using regex_substr

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

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?

Some help would be very much appreciated.

extract State Entity from String using regex_substr

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?

Source Link

asked Aug 9, 2017 at 19:26

Christopher

hwo to extract State Entity from String using regex_substr

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

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?

Some help would be very much appreciated.

oracle