For e.g. one > two > three > four > five
word four should be the resulting string.
I have this expression for matching the desired output: (?<=^(\w+\s>\s){3})(\w+).
But it returns the empty string using this:
REGEXP_SUBSTR (t.column, '(?<=^(\w+\s>\s){3})(\w+)').
What is wrong here?
Thank you.
Note: strings separated by '>' sign can be composed of two or more words. For example:
one > two > three > four1 four2 > five should return four1 four2
If it doesn't have to be a regular expression solution, use substr + instr combination (result_1).
Or, if it has to be regexp, and if string really looks as you posted it, fetch 4th word from it (result_2).
select trim(substr(col, instr(col, '>', 1, 3) + 1,
instr(col, '>', 1, 4) - instr(col, '>', 1, 3) - 1
)) result_1,
--
regexp_substr(col, '\w+', 1, 4) result_2
from your_table;
You need to convert the non-consuming pattern into a consuming one, and use additional arguments to make sure the REGEXP_SUBSTR returns the right capture:
REGEXP_SUBSTR (t.column, '^([^>]+\s+>\s+){3}\s*([^>]*[^[:space:]>])', 1, 1, NULL, 2)
Note there is no more lookbehind in the regex and since you need Group 2 value, the last argument is set to 2.
See an Oracle DB fiddle:
SELECT REGEXP_SUBSTR(
'one > two > three > four or more > five',
'^([^>]+\s+>\s+){3}\s*([^>]*[^[:space:]>])',
1, 1, NULL, 2) as Result from dual
Output:
