Oracle regular expression to return substring between specific start and end strings

Question

I'm trying to do a regex match to return a substring between a start and end point.

Given the following table:

WITH test AS (SELECT 'ABCD_EFGH_THIS_IJKL' AS thetext FROM DUAL
UNION SELECT 'ABAB CDCD EG BCD' FROM DUAL)
SELECT *
FROM test

I would want to return the results:

'THIS'
NULL

So it would match THIS in the first string, and nothing in the second string.

For this is safe to assume that ABCD_EFGH preceeds the text i want to match, and _ follows the text I want to match.

Thanks for any help!

EDIT: This needs to work on 10g. Sorry for not making that clear turbanoff.

turbanoff · Accepted Answer · 2012-06-06 05:55:37Z

1

use REGEXP_SUBSTR with 11g

WITH test AS (SELECT 'ABCD_EFGH_THIS_IJKL' AS thetext FROM DUAL
UNION SELECT 'ABAB CDCD EG BCD' FROM DUAL)
SELECT REGEXP_SUBSTR( TEST.THETEXT, 'ABCD_EFGH_([^_]*).*', 1, 1, 'i', 1)
FROM test

Edit

This can be done without using regular expressions.

WITH test AS (SELECT 'ABCD_EFGH_THIS_IJKL' AS thetext FROM DUAL
UNION SELECT 'ABAB CDCD EG BCD' FROM DUAL)
select TEST.thetext
     , instr(TEST.thetext, 'ABCD_EFGH_') + length('ABCD_EFGH_')  START_POS
     , instr(TEST.thetext, '_', length('ABCD_EFGH_') + 1) END_POS
     , substr
            (TEST.thetext
            ,instr(TEST.thetext, 'ABCD_EFGH_') + length('ABCD_EFGH_') --START_POS
            ,instr(TEST.thetext, '_', length('ABCD_EFGH_') + 1) - (instr(TEST.thetext, 'ABCD_EFGH_') + length('ABCD_EFGH_')) --END_POS - START_POS
            ) RESULT
FROM test

edited Jun 6, 2012 at 5:55

answered Jun 6, 2012 at 5:34

turbanoff

2,5096 gold badges43 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

StevieJ81 Over a year ago

I'm only on 10g unfortunately and I get an error saying too many arguments.

StevieJ81 Over a year ago

I knew I should have dropped regular expressions and just done it the old fashioned way. Thanks.

Collectives™ on Stack Overflow

Oracle regular expression to return substring between specific start and end strings

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related