0

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.

Community
  • 1
  • 1
Migs Isip
  • 1,450
  • 3
  • 23
  • 50

2 Answers2

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
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • can the string have multiple occurrences of PLE in the string? if so, do you only want to extract the substring till the first occurrence? – Vamsi Prabhala Dec 15 '16 at 02:24
  • yes that is possible. i was just testing right now and saw i have data that contained at least two `PLE` words. – Migs Isip Dec 15 '16 at 02:44
  • Hi, i have a related question in http://stackoverflow.com/questions/41156391/performance-and-readability-of-regexp-substr-vs-instr-and-substr stemming from this. – Migs Isip Dec 15 '16 at 04:25
1

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
  • Actually, that's the current code I have, but i found it "too long" to be understood by other programmers. But tested your approach it it was actually much faster (though only a second or so). let me re-phrase the question so i can paint a better picture. – Migs Isip Dec 15 '16 at 02:56
  • It's beyond me how any programmer can understand non-greedy matching in a regular expression but can't understand SUBSTR and INSTR. Not buying it... –  Dec 15 '16 at 03:41
  • Hi, i have a related question in http://stackoverflow.com/questions/41156391/performance-and-readability-of-regexp-substr-vs-instr-and-substr stemming from this. kindly take a look. thank you. – Migs Isip Dec 15 '16 at 04:27