Oracle RegExp_SubStr Text Between character and string

Question

Need help displaying the text between character '$' and string 'Request'

If we had this string

blah blah blah $60 blahblah blah Request

I would like to only print out

$60 blahblah blah

I've tried

 select   '$' || REGEXP_SUBSTR(notes,'([^\$]).*?(?=Request)')

this returns nothing. Sorry regular expressions are not my strong point

Note about your attempt: in a negated character class (the `[^ ... ]` thing), the dollar sign has no special meaning. Neither does the backslash. Only the dash `-`, the caret `^` and the closing bracket `]` have special meaning, and they can't be escaped - they must be placed in such a position that they can't have their special meaning, then they are interpreted to stand in for themselves. So, in your expression, `[^\$]` looks for characters that are not the dollar sign or backspace. In particular, you may inadvertently "trip" over a backspace in your input string. — , Jan 20 '18 at 05:25

score 1 · Answer 1 · answered Jan 18 '18 at 13:38

Oracle does not support lookahead.

Just match the string you want and wrap the parts you want to extract in a capture group and return that:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( notes ) AS
SELECT 'blah blah blah $60 blahblah blah Request' FROM DUAL;

Query 1:

SELECT REGEXP_SUBSTR(
         notes,             -- input
         '(\$.*?)Request',  -- pattern
         1,                 -- start at 1st character
         1,                 -- find 1st occurrence
         NULL,              -- flags
         1                  -- capture group to return
       ) AS request
FROM   table_name

Results:

|            REQUEST |
|--------------------|
| $60 blahblah blah  |

Oracle RegExp_SubStr Text Between character and string

1 Answers1