Oracle SQL 21.4.2中解析重复子串到对应列的技术问询

方法1：XML类型转换（推荐，保证对应关系） #

由于Domain Values列本质是XML片段，直接转成XML类型后提取成对的<Name>和<CodedValue>是最可靠的方式，能彻底避免两者错位的问题。

假设你的表名为mammal_data，结构如下：

CREATE TABLE mammal_data (
    "Mammal Groups" VARCHAR2(100),
    "Domain Values" CLOB
);

执行以下查询：

SELECT 
    md."Mammal Groups",
    x.name AS "Domain Name",
    x.coded_value AS "Domain Coded Value"
FROM mammal_data md,
     XMLTABLE(
         '/DomainValues/Code'
         PASSING XMLTYPE('<DomainValues>' || md."Domain Values" || '</DomainValues>')
         COLUMNS 
             name VARCHAR2(100) PATH 'Name',
             coded_value VARCHAR2(100) PATH 'CodedValue'
     ) x;

关键说明： #

• 先给Domain Values的片段补根节点<DomainValues>，使其成为合法XML文档
• 通过XMLTABLE将每个包含<Name>和<CodedValue>的<Code>节点（若你的实际标签结构不同，只需调整XPath路径）拆分为单行记录，天然保证每组名称与编码的对应关系

方法2：正则递归拆分（无XML支持时备选） #

如果你的数据库不支持XML类型，可使用递归CTE逐组提取成对数据：

WITH recursive_groups AS (
    SELECT 
        "Mammal Groups",
        "Domain Values",
        1 AS group_num,
        REGEXP_SUBSTR("Domain Values", '<Name>(.*?)</Name>.*?<CodedValue>(.*?)</CodedValue>', 1, 1, 'n', 1) AS name,
        REGEXP_SUBSTR("Domain Values", '<Name>(.*?)</Name>.*?<CodedValue>(.*?)</CodedValue>', 1, 1, 'n', 2) AS coded_value,
        REGEXP_INSTR("Domain Values", '<CodedValue>(.*?)</CodedValue>', 1, 1) + LENGTH(REGEXP_SUBSTR("Domain Values", '<CodedValue>(.*?)</CodedValue>', 1, 1)) AS next_pos
    FROM mammal_data
    WHERE "Domain Values" IS NOT NULL
    UNION ALL
    SELECT 
        "Mammal Groups",
        "Domain Values",
        group_num + 1,
        REGEXP_SUBSTR("Domain Values", '<Name>(.*?)</Name>.*?<CodedValue>(.*?)</CodedValue>', next_pos, 1, 'n', 1),
        REGEXP_SUBSTR("Domain Values", '<Name>(.*?)</Name>.*?<CodedValue>(.*?)</CodedValue>', next_pos, 1, 'n', 2),
        REGEXP_INSTR("Domain Values", '<CodedValue>(.*?)</CodedValue>', next_pos, 1) + LENGTH(REGEXP_SUBSTR("Domain Values", '<CodedValue>(.*?)</CodedValue>', next_pos, 1))
    FROM recursive_groups
    WHERE next_pos > 0 AND name IS NOT NULL
)
SELECT "Mammal Groups", name AS "Domain Name", coded_value AS "Domain Coded Value"
FROM recursive_groups
WHERE name IS NOT NULL
ORDER BY "Mammal Groups", group_num;

关键说明： #

• 用递归CTE从上次匹配结束的位置开始查找下一组，避免重复提取
• 正则采用非贪婪匹配.*?，确保每组<Name>与紧随其后的<CodedValue>对应
• 'n'参数让.匹配换行符，兼容多行XML片段

注意事项 #

• 若你的Domain Values标签结构与示例不同，只需调整XPath路径或正则表达式的匹配逻辑即可
• 优先选择XML方法，正则方案易受格式变动影响，稳定性不如XML处理

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