CODEWITHSUNDEEP
sqlregexoracle-database
John Smith
John Smith

Reputation: 69

Get a word after specific word using regexp_substr in sql oracle

I have tried 

select regexp_substr ('sys: error: This is a message ''123:'' for column EMP_NB', '[[:alpha:]_]+',1,9) from dual

I have column filled with this type of data.

sys: error: This is a message '123:' for column EMP_NB.  
sys: error: This is a message '45346:' for column EM_NM.  
sys: error: This is a message '78324f9:' for column DEPT_NO_VL.

I need an output like below this using regexp_substr in Oracle SQL. The challenge is the length of string tend to change and I need to extract only the characters after the column word in string.

Expected outputs:

EMP_NB
EM_NM
DEPT_NO_VL

Upvotes: 4

Views: 5838

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Just anchor the pattern to the end of the string:

select regexp_substr('sys: error: This is a message ''123:'' for column EMP_NB', '[[:alpha:]_]+$')
from dual

Here is a db<>fiddle.

Your example data has a period at the end. If that is part of the string, then you can use regexp_replace():

select regexp_replace(message, '^.*[^[:alpha:]_]([[:alpha:]_]+)[^[:alpha:]_]*$', '\1')
from (select 'sys: error: This is a message ''123:'' for column EMP_NB' as message from dual union all
      select 'sys: error: This is a message ''45346:'' for column EM_NM.' as message from dual union all
      select 'sys: error: This is a message ''78324f9:'' for column DEPT_NO_VL.' as message from dual
     ) x

Upvotes: 1

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626845

You may use

select regexp_substr ('sys: error: This is a message ''123:'' for column EMP_NB', 'column[[:space:]]*([[:alpha:]_]+)', 1, 1, NULL, 1) from dual

Here,

  • column - matches column word
  • [[:space:]]* - 0 or more whitespace chars
  • ([[:alpha:]_]+) - captures into Group 1 any one or more letters or underscores.

The value captured is returned only, since the last group ID argument is set to 1.

Upvotes: 3

Related Questions