PLSQL如何提取字符串中大括号{}外部的静态文本片段

完整实现SQL（保留原有变量列+新增静态片段列） #

WITH
     inputs (str) AS (
       SELECT '{user}_{yyyymmdd}_dailyreport.{type}'
       FROM   dual
     ),
     var_count AS (
       SELECT LENGTH(str) - LENGTH(REPLACE(str, '{')) AS cnt FROM inputs
     )
SELECT 
  level AS ord,
  SUBSTR(
    str, 
    INSTR(str, '{', 1, level) + 1, 
    INSTR(str, '}', 1, level) - INSTR(str, '{', 1, level) - 1
  ) AS data,
  CASE 
    WHEN level < (SELECT cnt FROM var_count) THEN 
      SUBSTR(
        str,
        INSTR(str, '}', 1, level) + 1,
        INSTR(str, '{', 1, level + 1) - INSTR(str, '}', 1, level) - 1
      )
    ELSE NULL 
  END AS "STATIC SEGMENTS"
FROM inputs
CONNECT BY level <= (SELECT cnt FROM var_count)
;

输出结果 #

ORD   DATA        STATIC SEGMENTS
----- ----------- --------------------
1     user        _
2     yyyymmdd    _dailyreport.
3     type        NULL

如果你只需要单独输出静态片段，可以使用下面的简化版本：

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 + 1) - INSTR(str, '}', 1, level) - 1
  ) AS "STATIC SEGMENTS"
FROM inputs
CONNECT BY level <= LENGTH(str) - LENGTH(REPLACE(str, '{')) - 1
;

输出结果和预期完全一致：

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

逻辑说明 #

• 静态片段固定位于前一个变量的闭合符}和后一个变量的起始符{之间，通过INSTR定位相邻两个符号的位置，计算截取长度后用SUBSTR提取即可
• 静态片段的总数始终等于变量总数减1，所以单独查询静态片段时递归层级比提取变量的查询少1级

内容的提问来源于stack exchange，提问作者yorpa