RegEx in Oracle SQL, positive look ahead

Question

I have a string and I'd like to capture everything before "\Close_Out":

string: \fileshare\R and G\123456\Close_Out\Warranty Letter.pdf

The only solution I have come up with uses positive lookahead, this works when I test it on https://regex101.com/

(.*)(?=\\Close_Out)

But now I need to use it in an Oracle SQL statement :

select REGEXP_SUBSTR('\\fileshare\R and G\123456\Close_Out\Warranty Letter.pdf', '(.*)(?=\\Close_Out)') from dual

and it does not work since (I think) look ahead is not supported. Can someone assist with an alternative expression that will work in sql

Answer 1

If regular expressions isn't a must, then substr + instr does the job:

SQL> with test (col) as
  2    (select '\fileshare\R and G\123456\Close_Out\Warranty Letter.pdf' from dual)
  3  select substr(col, 1, instr(col,'\Close_Out') - 1) result
  4  from test;

RESULT
-------------------------
\fileshare\R and G\123456

SQL>

Answer 2

Just for completness this REGEXP provides the result inclusive the \Close_Out

select REGEXP_SUBSTR('\\fileshare\R and G\123456\Close_Out\Warranty Letter.pdf', '.*\\Close_Out') reg from dual;


REG                                 
------------------------------------
\\fileshare\R and G\123456\Close_Out

To get the string before it use a subexpression - a part enclosed in paretheses and reference it with the subexpression parameter = 1 (last parameter - see details in documentation).

select REGEXP_SUBSTR('\\fileshare\R and G\123456\Close_Out\Warranty Letter.pdf', '(.*)\\Close_Out', 1, 1, null, 1) reg from dual;


REG                       
--------------------------
\\fileshare\R and G\123456