I want to capture the last word from the matched regexp. Here’s my query.
SELECT REGEXP_SUBSTR(
'The;quick;brown;fox;jumps;over;the;lazy;dog','^([^;]*;){5}([^;]*)') REF
FROM
DUAL
Desired result: over
Actual Result: The;quick;brown;fox;jumps;over
I can do subregex but it will affect the performance if there are million of records…
Nested Regex
SELECT REGEXP_SUBSTR(REGEXP_SUBSTR(
'The;quick;brown;fox;jumps;over;the;lazy;dog',
'^([^;]*;){5}([^;]*)'),'[^;]*$') REF
FROM
DUAL
CodePudding user response：
Don't use regular expressions if you are worried about performance (as they are slow), just use normal string functions:
SELECT SUBSTR(
value,
INSTR(value, ';', 1, 5) 1,
INSTR(value, ';', 1, 6) - INSTR(value, ';', 1, 5) - 1
) AS DATA
FROM table_name;
If you did want to use a regular expression then just extract the value of a capturing group:
SELECT REGEXP_SUBSTR(value, '(.*?);', 1, 6, NULL, 1) AS data
-- ^ Start from
-- ^ Occurrence
-- ^ Capturing group to extract
FROM table_name;
Which, for the sample data:
CREATE TABLE table_name ( value ) AS
SELECT 'The;quick;brown;fox;jumps;over;the;lazy;dog' FROM DUAL;
Both output:
DATA over
db<>fiddle here
CodePudding user response：
If you want to use REGEXP_SUBSTR then use its fourth parameter for the occurrence you are looking for:
SELECT REGEXP_SUBSTR(
'The;quick;brown;fox;jumps;over;the;lazy;dog',
'[^;] ',
1,
6) AS ref
FROM dual;