0

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.

tan
  • 19
  • 4

3 Answers3

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

Popeye
  • 35,427
  • 4
  • 10
  • 31
0

You can extract first word from any sentence with below code:

select regexp_substr ('TEXAS CITY, CALIFORNIA', '^[^ ]+',1 )test from dual;
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
0

Assuming that "CITY" won't always be the second word, this will get you only the word immediately preceding "CITY" for any string.

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

For more information, check out how regex works in Oracle SQL.

https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm

Tyler Morris
  • 11
  • 2
  • I'd say that your assumption is wrong. What if state name is NEW YORK? Your code returns just YORK. – Littlefoot Feb 05 '21 at 06:15
  • @Littlefoot Since the query was for "the word preceding CITY", I'd think that in the case of NEW YORK CITY, the intended behavior would, in fact, be to return "YORK". – Tyler Morris Feb 05 '21 at 12:36
  • hahahah! Right, if we look at it that way, you're absolutely right, @Tyler :) Though, I still think that OP would actually want "New York" instead. – Littlefoot Feb 05 '21 at 12:38