Using REGEXP_SUBSTR with Strings Qualifier

Question

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 character?

I would want to retrieve only the first characters before the occurrence of a string.

Example:

STRING_EXAMPLE
TREE_OF_APPLES

The Resulting Data set should only show only STRING_EXAM and TREE_OF_AP because PLE is my delimiter

Whenever i use the below REGEXP_SUBSTR, It gets only STRING_ because REGEXP_SUBSTR treats PLE as separate expressions (P, L and E), not as a single expression (PLE).

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

How can i do this without using numerous INSTRs and SUBSTRs?

Thank you.

Answer 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

select REGEXP_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

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

Answer 2

Why use regular expressions for this?

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

would be more efficient.

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