Regex to extract everything except city in Oracle-CodePudding

I have a string "Chicago IL something goes here" and want to extract left side and right side of the state - in this case " IL ".

I tried [^\s[A-Z]{2}\s] but not getting result but when I use specific characters [^\sIL\s] it works. I want to exclude any state

The output I expect is to extract "Chicago" and "something goes here"

CodePudding user response：

You can use:

SELECT REGEXP_SUBSTR(value, '^(.*?)\s[A-Z]{2}\s', 1, 1, NULL, 1) AS city,
       REGEXP_SUBSTR(value, '\s[A-Z]{2}\s(.*)$', 1, 1, NULL, 1) AS detail
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'Chicago IL something goes here' FROM DUAL UNION ALL
SELECT 'New York City NY something else' FROM DUAL;

Outputs:

CITY DETAIL

Chicago something goes here

New York City something else

CITY	DETAIL
Chicago	something goes here
New York City	something else

db<>fiddle here