Substring between 2 strings in Oracle SQL

Question

I need the capture the text between Detl Code Desc: and Ftyp Code Desc:

see the example below:

Detl Code Desc: CPS Leadership PD    Ftyp Code Desc: Flat Fee
Detl Code Desc: CPS Professional Develop. ED    Ftyp Code Desc

What would be the best way to do it? I tried regexp_substr but couldn't get it to work.

Thanks for the help.

Answer 1

Here's an example that uses a Common Table Expression (CTE) to set up some test data. It's like creating a test table inline and is a great way to provide data and examples to the folks you're asking for help. Make sure to include unexpected values like NULLS and incomplete data. You didn't specify so I assumed (dangerous!) that you want the first occurrence of the pattern in the string, hence the anchoring to the start of the string. I have a capturing group around the data between the 2 known strings which is what is returned. Note REGEXP_SUBSTR() returns NULL if the pattern is not found (ID 4). You may have to handle this depending on your specs.

with tbl(id, str) as (
  select 1, 'Detl Code Desc: CPS Leadership PD    Ftyp Code Desc: Flat Fee' from dual union all
  select 2, 'Detl Code Desc: CPS Professional Develop. ED    Ftyp Code Desc' from dual union all
  select 3, '' from dual union all
  select 4, 'Detl Code Desc: CPS Leadership PD' from dual
)
select id, regexp_substr(str, '^Detl Code Desc: (.*?) *Ftyp Code Desc', 1, 1, null, 1) detail_code_desc
from tbl;

        ID DETAIL_CODE_DESC                                              
---------- --------------------------------------------------------------
         1 CPS Leadership PD                                             
         2 CPS Professional Develop. ED                                  
         3                                                               
         4                                                               

4 rows selected.

In contrast, REGEXP_REPLACE() returns the original string if the pattern is not matched. Here I put capturing groups around all components of the string and returned just the 2nd one. Perhaps comparing the original and returned strings will let you know how to proceed if your match is not found. That is up to your specs. One way may lend itself better than the other to your error handling.

with tbl(id, str) as (
  select 1, 'Detl Code Desc: CPS Leadership PD    Ftyp Code Desc: Flat Fee' from dual union all
  select 2, 'Detl Code Desc: CPS Professional Develop. ED    Ftyp Code Desc' from dual union all
  select 3, '' from dual union all
  select 4, 'Detl Code Desc: CPS Leadership PD' from dual
)
select id, regexp_replace(str, '^(Detl Code Desc: )(.*?)( *Ftyp Code Desc)', '\2') detail_code_desc
from tbl;


        ID DETAIL_CODE_DESC             
---------- -----------------------------
         1 CPS Leadership PD: Flat Fee  
         2 CPS Professional Develop. ED 
         3                              
         4 Detl Code Desc: CPS Leadershi

4 rows selected.

P.S. Always show your work and what failed and any error messages when posting, it will help us help you.