oracle sql extract the word before a specific word

Question

Using oracle, how to get a specific word before a word. i got a sample script that i found here but its in reverse(it gets the word after a specific word which is the CITY)

select regexp_substr ('TEXAS CITY CALIFORNIA', 'CITY[[:space:]]([[:alpha:]]+)', 1, 1, NULL, 1 test from dual;

result: CALIFORNIA

what i want is to get is texas, but i need a dynamic script.

question from:https://stackoverflow.com/questions/66057770/oracle-sql-extract-the-word-before-a-specific-word

Answer 1

Efficient way to achieve the desired result is to use substr and instr as follows:

substr('TEXAS CITY, CALIFORNIA',1, instr('TEXAS CITY, CALIFORNIA',' CITY'))

db<>fiddle