Using REGEXP_SUBSTR with a String Classifier

Question

Getting examples from similar threads, Delete all characters after a specific character in PL / SQL as well as How to select a substring in Oracle SQL before a specific character?

I would like to get only the first characters before the appearance of the string.

Example:

STRING_EXAMPLE
TREE_OF_APPLES

The resulting dataset should only show STRING_EXAMand TREE_OF_AP, because PLE- my separator

Whenever I use below REGEXP_SUBSTR, it gets only STRING_because it is REGEXP_SUBSTRtreated PLEas separate expressions (P, L and E), and not as a single expression (PLE).

SELECT REGEXP_SUBSTR('STRING_EXAMPLE','[^PLE]+',1,1) from dual;

How can I do this without using numerous INSTR and SUBSTR?

Thank.

Answer 1

, [^PLE], , P L E. PLE . ,

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

PLE .

PLE ,

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

Answer 2

?

select substr(colname, 1, instr(colname, 'PLE')-1) from...

.

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

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