Extract text outside an opening and closing curly brace "{}" in PLSQL-CodePudding

I have a file pattern, for example {user}_{yyyymmdd}_dailyreport.{type} and I have this to extract the values between an opening and closing curly brace {}

with
     inputs (str) as (
       select '{user}_{yyyymmdd}_dailyreport.{type}'
       from   dual
     )
select level as ord,
substr(str, instr(str, '{', 1, level)   1, instr(str, '}', 1, level) - instr(str, '{', 1, level) - 1) as data
from   inputs
connect by level <= length(str) - length(replace(str, '{'))
;

which outputs this

ORD   DATA                                  
----- ----------
1     user                                   
2     yyyymmdd                               
3     type

In a separate column, I want to obtain this

ORD   STATIC SEGMENTS                                  
----- ----------
1     _                                   
2     _dailyreport.

but not sure how to get that. It basically represents every text not enclosed in a pair of opening and closing brace

CodePudding user response：

Using regexp_substr and treating { and } as delimiters, you want the 4th word:

regexp_substr(str,'[^{}] ',1,4);

CodePudding user response：

Help yourself! How? Replace curly brackets with another delimiter (such as semi-colon); the rest is then simple.

SQL> with
  2  inputs (str) as
  3    (select '{user}_{yyyymmdd}_dailyreport.{type}'
  4     from   dual
  5    ),
  6  replaced as
  7    (select trim(both ';' from replace(replace(str, '{', ';'), '}', ';')) val
  8     from inputs
  9    )
 10  select level ord,
 11         regexp_substr(val, '[^;] ', 1, level) result
 12  from replaced
 13  connect by level <= regexp_count(val, ';')   1;

       ORD RESULT
---------- --------------------
         1 user
         2 _
         3 yyyymmdd
         4 _dailyreport.
         5 type

SQL>

CodePudding user response：

If - as you say in a reply in the Comments section - the initial and final substrings will always be braced expressions, this should be easy. You are looking for the substrings between the n'th closing brace and the (n 1)'st opening brace; so you can use pretty much the same computation, modified accordingly.

with
     inputs (str) as (
       select '{user}_{yyyymmdd}_dailyreport.{type}'
       from   dual
     )
select level as ord,
substr(str, instr(str, '{', 1, level)   1, 
       instr(str, '}', 1, level) - instr(str, '{', 1, level) - 1) as data,
substr(str, instr(str, '}', 1, level)   1, 
       instr(str, '{', 1, level   1) - instr(str, '}', 1, level) - 1) as static_segment
from   inputs
connect by level <= length(str) - length(replace(str, '{'))
;

ORD DATA       STATIC_SEGMENT    
--- ---------- ------------------
  1 user       _                 
  2 yyyymmdd   _dailyreport.     
  3 type