如何从数据库列提取UID后的四位字符并生成逗号分隔列表？

存在一张名为my_table的表，其中source_column列的内容是由; 分隔的字符串，包含一个或多个UID-xxxx格式的片段（UID-后固定跟四位字母数字字符）。

示例数据 #

source_column
--------------------------------------------
UID-12AB ; blah blah ; UID-CD34 ; blah blah
UID-56EF ; blah blah
UID-GH78 ; UID-90IJ ; UID-KL12 ; blah blah ; UID-34MN ; blah blah

需求 #

提取所有UID-后的四位字母数字部分，去掉UID-前缀，最终用逗号+空格分隔列出，忽略无关内容，得到如下结果：

source_column                                                       extracted_column
--------------------------------------------                        -----------------------
UID-12AB ; blah blah ; UID-CD34 ; blah blah                         12AB, CD34
UID-56EF ; blah blah                                                56EF
UID-GH78 ; UID-90IJ ; UID-KL12 ; blah blah ; UID-34MN ; blah blah   GH78, 90IJ, KL12, 34MN

针对不同数据库，提供对应的实现方式：

PostgreSQL #

使用regexp_matches全局提取匹配项，再用string_agg拼接结果：

SELECT
    source_column,
    string_agg(match[1], ', ') AS extracted_column
FROM my_table,
     regexp_matches(source_column, 'UID-([A-Z0-9]{4})', 'g') AS match
GROUP BY source_column;

• regexp_matches的'g'参数表示全局匹配，捕获每个UID-后的四位字母数字
• string_agg将所有捕获的片段用, 连接

MySQL 8.0+/MariaDB 10.5+ #

通过递归CTE遍历提取所有匹配项，再用GROUP_CONCAT拼接：

WITH RECURSIVE cte AS (
    SELECT
        source_column,
        REGEXP_SUBSTR(source_column, 'UID-([A-Z0-9]{4})', 1, 1, 'c', 1) AS uid_part,
        1 AS idx
    FROM my_table
    WHERE REGEXP_SUBSTR(source_column, 'UID-([A-Z0-9]{4})', 1, 1) IS NOT NULL
    UNION ALL
    SELECT
        c.source_column,
        REGEXP_SUBSTR(c.source_column, 'UID-([A-Z0-9]{4})', 1, c.idx+1, 'c', 1),
        c.idx+1
    FROM cte c
    WHERE REGEXP_SUBSTR(c.source_column, 'UID-([A-Z0-9]{4})', 1, c.idx+1) IS NOT NULL
)
SELECT
    source_column,
    GROUP_CONCAT(uid_part SEPARATOR ', ') AS extracted_column
FROM cte
GROUP BY source_column
ORDER BY source_column;

• REGEXP_SUBSTR的最后一个参数1表示返回捕获组的内容
• 'c'参数开启大小写区分，不需要的话可以移除

Oracle #

用CONNECT BY生成匹配次数的行，结合REGEXP_SUBSTR提取，最后用LISTAGG拼接：

SELECT
    source_column,
    LISTAGG(REGEXP_SUBSTR(source_column, 'UID-([A-Z0-9]{4})', 1, level, 'c', 1), ', ')
        WITHIN GROUP (ORDER BY level) AS extracted_column
FROM my_table
CONNECT BY LEVEL <= REGEXP_COUNT(source_column, 'UID-[A-Z0-9]{4}')
    AND PRIOR source_column = source_column
    AND PRIOR SYS_GUID() IS NOT NULL
GROUP BY source_column;

• REGEXP_COUNT统计每个字符串中符合格式的UID数量
• CONNECT BY LEVEL生成对应行数，逐行提取每个UID片段

SQL Server #

通过递归CTE逐个提取匹配项，再用STRING_AGG拼接：

WITH RECURSIVE cte AS (
    SELECT
        source_column,
        SUBSTRING(source_column, PATINDEX('%UID-[A-Z0-9]{4}%', source_column)+4, 4) AS uid_part,
        STUFF(source_column, 1, PATINDEX('%UID-[A-Z0-9]{4}%', source_column)+7, '') AS remaining_str
    FROM my_table
    WHERE PATINDEX('%UID-[A-Z0-9]{4}%', source_column) > 0
    UNION ALL
    SELECT
        c.source_column,
        SUBSTRING(c.remaining_str, PATINDEX('%UID-[A-Z0-9]{4}%', c.remaining_str)+4, 4),
        STUFF(c.remaining_str, 1, PATINDEX('%UID-[A-Z0-9]{4}%', c.remaining_str)+7, '')
    FROM cte c
    WHERE PATINDEX('%UID-[A-Z0-9]{4}%', c.remaining_str) > 0
)
SELECT
    source_column,
    STRING_AGG(uid_part, ', ') AS extracted_column
FROM cte
GROUP BY source_column
ORDER BY source_column;

• PATINDEX定位第一个匹配的UID位置，SUBSTRING提取四位字符
• STUFF截断已处理的字符串，递归处理剩余部分

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