Oracle SQL中如何提取特定字符串后的指定信息值

当源表中某列存储的是顺序不固定的键值对（如“产品编码：XXX，产品名称：YYY”这类格式），substr+instr的组合会因为顺序变化失效，推荐使用**正则表达式函数REGEXP_SUBSTR**来提取指定键对应的值，以下是具体实现方案：

核心方法：利用REGEXP_SUBSTR匹配捕获组 #

Oracle的REGEXP_SUBSTR支持通过正则捕获组提取目标内容，无需依赖键值对的顺序。语法示例：

REGEXP_SUBSTR(源列名, '键名：([^分隔符]+)', 1, 1, 'i', 1)

参数说明：

• 第1个参数：存储键值对的源列
• 第2个参数：正则模式，键名：匹配目标键，([^分隔符]+)捕获键之后到分隔符前的所有内容（分隔符根据你的数据实际调整，比如逗号、分号等）
• 第3个参数：起始匹配位置（固定为1）
• 第4个参数：匹配第N次出现的结果（固定为1）
• 第5个参数：'i'表示不区分大小写，忽略键名的大小写差异
• 第6个参数：提取第1个捕获组的内容（也就是括号里匹配到的值）

完整插入示例 #

假设源表为source_table，存储键值对的列是detail_info；目标表为target_table，包含product_code、product_name、spec、unit字段，执行以下SQL即可完成提取插入：

INSERT INTO target_table (product_code, product_name, spec, unit)
SELECT
  -- 提取产品编码
  REGEXP_SUBSTR(detail_info, '产品编码：([^，；]+)', 1, 1, 'i', 1) AS product_code,
  -- 提取产品名称
  REGEXP_SUBSTR(detail_info, '产品名称：([^，；]+)', 1, 1, 'i', 1) AS product_name,
  -- 提取规格
  REGEXP_SUBSTR(detail_info, '规格：([^，；]+)', 1, 1, 'i', 1) AS spec,
  -- 提取单位
  REGEXP_SUBSTR(detail_info, '单位：([^，；]+)', 1, 1, 'i', 1) AS unit
FROM source_table;

注意事项 #

1. 分隔符适配：如果你的数据中键值对的分隔符不是逗号/分号，需要修改正则中的[^，；]部分，比如分隔符是换行就换成[^ ]，是竖线就换成[^|]。
2. 空值处理：如果某条数据中没有目标键，REGEXP_SUBSTR会返回NULL，可以用NVL函数替换为默认值，比如NVL(REGEXP_SUBSTR(...), '无数据')。
3. 特殊字符转义：如果键名包含正则特殊字符（如.、*），需要在正则中用\转义，比如键名是“产品.编码”，则正则写成产品\.编码：([^，；]+)。

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