I need to extract a substring from a string and Nth occurance
Example:
Abc-defg-hijkl-mno pqr-1245
Abc-defg-hijkl-mno ab-8748
The output should be
pqr
ab
I tried
substr(text,
instr(text, ’Abc-defg-hijkl-mno’),
instr(text, ’-’) - instr(text, ’Abc-defg-hijkl-mno’))
Any help is appreciated. Thanks
New contributor
Bhanu Shankar Buddi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
We can use REGEXP_SUBSTR() here with a capture group:
SELECT REGEXP_SUBSTR(val, '([a-z]+)-[0-9]+$', 1, 1, NULL, 1)
FROM yourTable;
The regex pattern used matches the last component of the string (ending with $ marker), and captures the leading letter portion before the hyphen in the first capture group.
Here is a running demo.