Oracle SQL非REGEXP_SUBSTR提取分隔符串中product与version字段咨询

1. 不使用REGEXP_SUBSTR提取产品编号 #

使用Oracle内置的INSTR和SUBSTR组合实现定位截取，兼容所有Oracle版本，性能优于正则实现：

核心逻辑 #

• 用INSTR定位product=字符串的起始位置
• 计算产品编号的起始位置为product=起始位置+8（product=共8个字符）
• 向后查找第一个&的位置作为产品编号的结束边界，若product在字符串末尾则以整个字符串长度为边界
• 用SUBSTR截取边界中间的内容

示例代码 #

SELECT 
  SUBSTR(
    param_str,
    INSTR(param_str, 'product=') + 8,
    CASE 
      WHEN INSTR(param_str, '&', INSTR(param_str, 'product=')) > 0 
      THEN INSTR(param_str, '&', INSTR(param_str, 'product=')) - (INSTR(param_str, 'product=') + 8)
      ELSE LENGTH(param_str) - (INSTR(param_str, 'product=') + 7)
    END
  ) AS product_id
FROM your_table;

注：将param_str替换为你表中存储参数字符串的字段名，your_table替换为实际表名。

2. 新增版本号展示列的最优方案（无重复行） #

根据使用场景选择对应方案，所有方案均为单表扫描无关联，不会产生重复行：

场景1：临时查询展示 #

直接在SELECT语句中新增版本号的截取逻辑即可，无需修改表结构，性能最优：

SELECT 
  -- 原有字段
  SUBSTR(
    param_str,
    INSTR(param_str, 'product=') + 8,
    CASE 
      WHEN INSTR(param_str, '&', INSTR(param_str, 'product=')) > 0 
      THEN INSTR(param_str, '&', INSTR(param_str, 'product=')) - (INSTR(param_str, 'product=') + 8)
      ELSE LENGTH(param_str) - (INSTR(param_str, 'product=') + 7)
    END
  ) AS product_id,
  SUBSTR(
    param_str,
    INSTR(param_str, 'version=') + 8,
    CASE 
      WHEN INSTR(param_str, '&', INSTR(param_str, 'version=')) > 0 
      THEN INSTR(param_str, '&', INSTR(param_str, 'version=')) - (INSTR(param_str, 'version=') + 8)
      ELSE LENGTH(param_str) - (INSTR(param_str, 'version=') + 7)
    END
  ) AS version
FROM your_table;

场景2：频繁使用两个拆分字段（Oracle 11g及以上） #

优先创建虚拟列（计算列），无需额外存储，数据自动和原参数字段同步，不需要维护一致性：

ALTER TABLE your_table 
ADD (
  product_id VARCHAR2(50) GENERATED ALWAYS AS (
    SUBSTR(
      param_str,
      INSTR(param_str, 'product=') + 8,
      CASE 
        WHEN INSTR(param_str, '&', INSTR(param_str, 'product=')) > 0 
        THEN INSTR(param_str, '&', INSTR(param_str, 'product=')) - (INSTR(param_str, 'product=') + 8)
        ELSE LENGTH(param_str) - (INSTR(param_str, 'product=') + 7)
      END
    )
  ) VIRTUAL,
  version VARCHAR2(50) GENERATED ALWAYS AS (
    SUBSTR(
      param_str,
      INSTR(param_str, 'version=') + 8,
      CASE 
        WHEN INSTR(param_str, '&', INSTR(param_str, 'version=')) > 0 
        THEN INSTR(param_str, '&', INSTR(param_str, 'version=')) - (INSTR(param_str, 'version=') + 8)
        ELSE LENGTH(param_str) - (INSTR(param_str, 'version=') + 7)
      END
    )
  ) VIRTUAL
);

创建后可直接查询product_id和version字段使用。

场景3：Oracle 11g以下版本 #

创建视图封装拆分逻辑，使用时直接查询视图即可，无重复行风险：

CREATE VIEW your_table_v AS
SELECT 
  t.*,
  SUBSTR(
    param_str,
    INSTR(param_str, 'product=') + 8,
    CASE 
      WHEN INSTR(param_str, '&', INSTR(param_str, 'product=')) > 0 
      THEN INSTR(param_str, '&', INSTR(param_str, 'product=')) - (INSTR(param_str, 'product=') + 8)
      ELSE LENGTH(param_str) - (INSTR(param_str, 'product=') + 7)
    END
  ) AS product_id,
  SUBSTR(
    param_str,
    INSTR(param_str, 'version=') + 8,
    CASE 
      WHEN INSTR(param_str, '&', INSTR(param_str, 'version=')) > 0 
      THEN INSTR(param_str, '&', INSTR(param_str, 'version=')) - (INSTR(param_str, 'version=') + 8)
      ELSE LENGTH(param_str) - (INSTR(param_str, 'version=') + 7)
    END
  ) AS version
FROM your_table t;

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