Oracle如何从MATCH_DETAILS列提取ID并按行返回单个数值

要把MATCH_DETAILS列中“Possible QSIDs: ”后的逗号分隔ID拆成单独行返回，不同数据库的实现方式略有不同，以下是主流数据库的解决方案：

Oracle 数据库 #

方法1：使用REGEXP_SUBSTR + CONNECT BY #

先提取出干净的ID列表，再通过递归拆分每一个ID：

WITH split_data AS (
    SELECT 
        TRIM(SUBSTR(cd.match_details,
                   INSTR(cd.match_details, 'Possible QSIDs: ', 1) + 16,
                   LENGTH(cd.match_details) - INSTR(cd.match_details, 'Possible QSIDs: ', 1) - 16)
            ) AS qsid_list
    FROM scv_client_details cd
    WHERE cd.match_details LIKE 'Matched with multiple QSIDs%'
)
SELECT TRIM(REGEXP_SUBSTR(qsid_list, '[^,]+', 1, LEVEL)) AS qsid
FROM split_data
CONNECT BY REGEXP_SUBSTR(qsid_list, '[^,]+', 1, LEVEL) IS NOT NULL
AND PRIOR qsid_list = qsid_list
AND PRIOR SYS_GUID() IS NOT NULL;

• TRIM用于清理ID前后的空格或换行符
• CONNECT BY配合REGEXP_SUBSTR实现逐行拆分，PRIOR SYS_GUID()避免产生笛卡尔积

方法2：使用XMLTABLE #

通过将逗号分隔字符串转为XML格式，再解析为行：

SELECT TRIM(xt.qsid) AS qsid
FROM scv_client_details cd,
     XMLTABLE(('"' || REPLACE(SUBSTR(cd.match_details,
                                     INSTR(cd.match_details, 'Possible QSIDs: ', 1) + 16,
                                     LENGTH(cd.match_details) - INSTR(cd.match_details, 'Possible QSIDs: ', 1) - 16),
                             ',', '","') || '"')
              COLUMNS qsid VARCHAR2(20) PATH '.') xt
WHERE cd.match_details LIKE 'Matched with multiple QSIDs%';

MySQL 数据库（8.0+） #

方法1：递归CTE拆分 #

适合所有MySQL 8.0及以上版本：

WITH RECURSIVE split_data AS (
    SELECT 
        SUBSTR(cd.match_details,
               INSTR(cd.match_details, 'Possible QSIDs: ', 1) + 16,
               LENGTH(cd.match_details) - INSTR(cd.match_details, 'Possible QSIDs: ', 1) - 16) AS qsid_list,
        1 AS pos
    FROM scv_client_details cd
    WHERE cd.match_details LIKE 'Matched with multiple QSIDs%'
    UNION ALL
    SELECT 
        SUBSTRING(qsid_list, LOCATE(',', qsid_list) + 1),
        pos + 1
    FROM split_data
    WHERE LOCATE(',', qsid_list) > 0
)
SELECT TRIM(SUBSTRING_INDEX(qsid_list, ',', 1)) AS qsid
FROM split_data
WHERE TRIM(SUBSTRING_INDEX(qsid_list, ',', 1)) != '';

方法2：JSONTABLE（8.0.4+） #

利用JSON函数快速拆分字符串：

SELECT TRIM(xt.qsid) AS qsid
FROM scv_client_details cd,
     JSON_TABLE(
         CONCAT('["', REPLACE(SUBSTR(cd.match_details,
                                     INSTR(cd.match_details, 'Possible QSIDs: ', 1) + 16,
                                     LENGTH(cd.match_details) - INSTR(cd.match_details, 'Possible QSIDs: ', 1) - 16),
                             ',', '","'), '"]'),
         '$[*]' COLUMNS qsid VARCHAR(20) PATH '$'
     ) xt
WHERE cd.match_details LIKE 'Matched with multiple QSIDs%';

SQL Server 数据库（2016+） #

直接使用内置的STRING_SPLIT函数，简单高效：

SELECT TRIM(value) AS qsid
FROM scv_client_details cd
CROSS APPLY STRING_SPLIT(
    SUBSTRING(cd.match_details,
              CHARINDEX('Possible QSIDs: ', cd.match_details) + 16,
              LEN(cd.match_details) - CHARINDEX('Possible QSIDs: ', cd.match_details) - 16),
    ','
)
WHERE cd.match_details LIKE 'Matched with multiple QSIDs%'
AND TRIM(value) != '';

注意事项 #

• 所有方案都加入了TRIM和空值过滤，避免因原字符串末尾的逗号产生空行
• 请根据你使用的数据库版本选择对应的方法，部分函数需要特定版本支持

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