如何用SQL提取字符串中所有匹配正则的element标签片段

首先建议优化正则表达式为 <element[^>]*><\/element>，原正则可能出现匹配截断问题，优化后的规则匹配<element开头、之后紧跟任意非>字符直到标签闭合，再匹配</element>结尾，准确性更高。
以下是不同主流数据库的实现方案：

MySQL 8.0+ 实现 #

通过递归CTE遍历提取所有匹配项：

WITH RECURSIVE matches AS (
    SELECT 
        id,
        content,
        1 AS match_index,
        REGEXP_SUBSTR(content, '<element[^>]*><\\/element>', 1, 1) AS matched_element
    FROM your_table
    UNION ALL
    SELECT 
        id,
        content,
        match_index + 1,
        REGEXP_SUBSTR(content, '<element[^>]*><\\/element>', 1, match_index + 1)
    FROM matches
    WHERE matched_element IS NOT NULL
)
SELECT id, matched_element 
FROM matches 
WHERE matched_element IS NOT NULL
ORDER BY id, match_index;

使用时将your_table替换为实际表名，content替换为存储HTML内容的字段名，id替换为表的主键字段即可。

PostgreSQL 实现 #

PostgreSQL内置regexp_matches函数支持全局匹配，实现更简洁：

SELECT 
    id,
    unnest(regexp_matches(content, '<element[^>]*><\/element>', 'g')) AS matched_element
FROM your_table;

参数g代表全局匹配，返回所有符合规则的结果，unnest函数将数组格式的匹配结果展开为多行。

Oracle 实现 #

通过CONNECT BY循环提取所有匹配项：

SELECT 
    id,
    REGEXP_SUBSTR(content, '<element[^>]*><\/element>', 1, LEVEL) AS matched_element
FROM your_table
CONNECT BY LEVEL <= REGEXP_COUNT(content, '<element[^>]*><\/element>')
    AND PRIOR id = id
    AND PRIOR SYS_GUID() IS NOT NULL;

REGEXP_COUNT先统计每个字段的匹配数量，再通过LEVEL遍历提取所有匹配结果，PRIOR SYS_GUID()是为了避免循环报错。

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