Oracle SQL提取多行XML字段中指定字符串间的内容

核心问题分析 #

你之前使用的正则表达式\:(.+)\~完全不匹配目标字符串的结构，导致返回NULL。需要针对XML转义后的标签结构编写正确的正则规则。

解决方案 #

1. 提取包含指定属性名（如attr4）的标签内容 #

适用于从第一行提取7,5，即使属性名带括号（如attribute (m)）也能处理：

-- 提取包含attr4的标签内数值
SELECT REGEXP_SUBSTR(
    '<attr name="attr1; attr2; attr3; attr4">7,5</attr>
<attr name="attr5; attr6; attr7; attr8">3,5</attr>',
    '<attr name="[^"]*attr4[^"]*">([^<]+)</attr>',
    1, 1, NULL, 1
) AS first_target_value
FROM dual;

正则规则解释：

• <attr name="：匹配转义后的标签开头
• [^"]*attr4[^"]*：匹配包含attr4的属性名称部分（[^"]*表示任意非双引号转义的字符）
• ">：匹配转义后的标签属性结束部分
• ([^<]+)：捕获标签内的数值（[^<]+表示任意非<转义的字符，即目标内容）
• </attr>：匹配转义后的标签结尾

2. 处理带括号的属性名 #

如果属性名是attribute (m)，需要转义正则中的特殊字符(和)：

-- 提取包含带括号属性名的标签内数值
SELECT REGEXP_SUBSTR(
    '<attr name="attr1; attribute (m)">7,5</attr>',
    '<attr name="[^"]*attribute \(m\)[^"]*">([^<]+)</attr>',
    1, 1, NULL, 1
) AS target_value_with_paren
FROM dual;

3. 提取第二行的3,5 #

可以通过两种方式实现：

方法1：按标签出现次数提取

SELECT REGEXP_SUBSTR(
    '<attr name="attr1; attr2; attr3; attr4">7,5</attr>
<attr name="attr5; attr6; attr7; attr8">3,5</attr>',
    '<attr name="[^"]*">([^<]+)</attr>',
    1, 2, NULL, 1
) AS second_target_value
FROM dual;

方法2：按第二行的属性名（如attr8）提取

SELECT REGEXP_SUBSTR(
    '<attr name="attr1; attr2; attr3; attr4">7,5</attr>
<attr name="attr5; attr6; attr7; attr8">3,5</attr>',
    '<attr name="[^"]*attr8[^"]*">([^<]+)</attr>',
    1, 1, NULL, 1
) AS second_target_value
FROM dual;

关键参数说明 #

Oracle的REGEXP_SUBSTR函数第6个参数用于指定捕获组编号，这里我们用1来提取正则中括号()包裹的目标内容。

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