Oracle SQL解析字符串中可变数量@name键的引号值需求

需求说明 #

从给定的路径字符串中，提取所有@name='xxx'格式里的xxx内容，每个字符串中该格式的出现数量不固定，最终将同一字符串的所有提取值用空格分隔输出，基于Oracle SQL实现。

示例输入 #

/content/folder[@name='LocationB']/report[@name='Report29']
/content/folder[@name='LocationA']/folder[@name='Reports']/report[@name='ReportAAA']
/content/folder[@name='LocationA']/folder[@name='Reports']/folder[@name='FolderC']/folder[@name='FolderCD']/report[@name='ReportBBB']
/transientStateFolder/agentState[@name='2019-05-09T19:47:03.029Z']/reportView[@name='Report232']
CAMID("corp.xyz.net:u:254679b8195bf547a8b894de8a6ea822")/folder[@name='My Folders']/folder[@name='Subscriptions']/reportView[@name='My Daily Numbers']

期望输出 #

LocationB Report29
LocationA Reports ReportAAA
LocationA Reports FolderC FolderCD ReportBBB
2019-05-09T19:47:03.029Z Report232
My Folders Subscriptions My Daily Numbers

解决方案 #

1. 测试数据准备 #

先创建测试表并插入示例数据：

CREATE TABLE test_paths (path_str VARCHAR2(1000));
INSERT INTO test_paths VALUES ('/content/folder[@name=''LocationB'']/report[@name=''Report29'']');
INSERT INTO test_paths VALUES ('/content/folder[@name=''LocationA'']/folder[@name=''Reports'']/report[@name=''ReportAAA'']');
INSERT INTO test_paths VALUES ('/content/folder[@name=''LocationA'']/folder[@name=''Reports'']/folder[@name=''FolderC'']/folder[@name=''FolderCD'']/report[@name=''ReportBBB'']');
INSERT INTO test_paths VALUES ('/transientStateFolder/agentState[@name=''2019-05-09T19:47:03.029Z'']/reportView[@name=''Report232'']');
INSERT INTO test_paths VALUES ('CAMID("corp.xyz.net:u:254679b8195bf547a8b894de8a6ea822")/folder[@name=''My Folders'']/folder[@name=''Subscriptions'']/reportView[@name=''My Daily Numbers'']');

2. 提取并聚合的SQL语句 #

使用正则表达式结合分层查询实现动态提取，最终聚合为空格分隔的字符串：

SELECT 
    LISTAGG(extracted_name, ' ') WITHIN GROUP (ORDER BY pos) AS result
FROM (
    SELECT 
        t.path_str,
        -- 提取@name引号内的内容，取第一个捕获组
        REGEXP_SUBSTR(t.path_str, '@name=''([^'']+)''', 1, LEVEL, NULL, 1) AS extracted_name,
        LEVEL AS pos
    FROM test_paths t
    CONNECT BY 
        -- 循环次数等于@name格式的出现次数
        LEVEL <= REGEXP_COUNT(t.path_str, '@name=''([^'']+)''')
        -- 确保同一原始字符串的行关联
        AND PRIOR t.path_str = t.path_str
        -- 避免CONNECT BY循环错误
        AND PRIOR SYS_GUID() IS NOT NULL
)
GROUP BY path_str
ORDER BY path_str;

代码说明 #

• REGEXP_COUNT：统计每个字符串中@name='xxx'的出现次数，作为分层查询的循环上限。
• REGEXP_SUBSTR：通过指定LEVEL参数，依次提取第N个匹配项的捕获组内容（即引号内的字符串）。
• CONNECT BY：生成对应数量的行，每个行存储一个提取出的name值。
• LISTAGG：将同一原始字符串的所有提取值按顺序用空格连接，生成最终输出格式。

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