I have a paragraph and I need to extract a word from it.
Investigator: O'Toole, Tim
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:
- Source text - case_desc.
- Position to start searching the source text.
- Occurrence of pattern that is required to be extracted.
- Match properties - 'i' is ignore case.
- 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.