Toad World® Forums

Extract a word in a paragraph using regex in oracle

Hi all,

I have a paragraph and I need to extract a word from it.

Attention:
Investigator: O'Toole, Tim  
Suspect Name:
Ezekiel, David

Account Number:
4323232

DEsired output:

Ezekiel, David

What I tried:

regexp_extract([Case_Desc],'Name : (.+)Account')

The above regex worked in tableau but for some reasons, toad for Oracle does not support this. Any help on this is really appreciated.

Thanks in advance.

I don't see where REGEXP_EXTRACT is supported as a regular expression function by Oracle... try using REGEXP_SUBSTR() instead.

This works for me, using your exact example:

 select regexp_substr(case_desc, '.*name:\s?(.*?).*', 1, 1, 'i', 1) from dual;

The parameters are:

  1. Source text - case_desc.
  2. Pattern.
  3. Position to start searching the source text.
  4. Occurrence of pattern that is required to be extracted.
  5. Match properties - 'i' is ignore case.
  6. Sub-expression required, here there is one sub expression '(.*?)' so we use number 1.

The above returns 'Ezekiel, David' with no leading or trailing white-space etc.

Cheers,
Norm. [TeamT]