Using Regexp_substr With Strings Qualifier

Getting Examples from similar Stack Overflow threads, Remove all characters after a specific character in PL/SQL and How to Select a substring in Oracle SQL up to a specific char

Solution 1:

The problem with your query is that if you use [^PLE] it would match any characters other than P or L or E. You are looking for an occurence of PLE consecutively. So, use

selectREGEXP_SUBSTR(colname,'(.+)PLE',1,1,null,1) 
from tablename

This returns the substring up to the last occurrence of PLE in the string.

If the string contains multiple instances of PLE and only the substring up to the first occurrence needs to be extracted, use

selectREGEXP_SUBSTR(colname,'(.+?)PLE',1,1,null,1) 
from tablename

Solution 2:

Why use regular expressions for this?

selectsubstr(colname, 1, instr(colname, 'PLE')-1) from...

would be more efficient.

with
     inputs( colname ) as (
       select'FIRST_EXAMPLE'from dual unionallselect'IMPLEMENTATION'from dual unionallselect'PARIS'from dual unionallselect'PLEONASM'from dual
     )
select colname, substr(colname, 1, instr(colname, 'PLE')-1) asresultfrom   inputs
;

COLNAME          RESULT--------------   ----------
FIRST_EXAMPLE    FIRST_EXAM
IMPLEMENTATION   IM
PARIS   
PLEONASM