SQL如何提取列内尖括号邮箱子串并通过explode拆分为多行

原始数据与目标 #

原始表结构样例：

目标输出为单个邮箱占一行，其余列值保持不变：

核心处理逻辑 #

• 第一步：清洗Header 3字段，通过正则替换移除所有尖括号<>，得到纯逗号分隔的邮箱字符串
• 第二步：按逗号为分隔符将字符串切分为数组
• 第三步：调用数组展开函数将数组元素拆分为独立行，同时保留其余字段的原始值，额外处理逗号前后的多余空格、过滤拆分产生的空值即可。

不同SQL引擎的实现代码 #

Hive / Spark SQL #

SELECT
    `Header 1`,
    `Header 2`,
    TRIM(single_email) AS `Header 3`
FROM 你的表名
LATERAL VIEW EXPLODE(
    SPLIT(
        REGEXP_REPLACE(`Header 3`, '[<>]', ''),
        ','
    )
) t AS single_email
WHERE TRIM(single_email) != '';

PostgreSQL #

SELECT
    "Header 1",
    "Header 2",
    TRIM(unnest(string_to_array(regexp_replace("Header 3", '[<>]', '', 'g'), ','))) AS "Header 3"
FROM 你的表名;

BigQuery #

SELECT
    `Header 1`,
    `Header 2`,
    TRIM(single_email) AS `Header 3`
FROM 你的表名,
UNNEST(SPLIT(REGEXP_REPLACE(`Header 3`, r'[<>]', ''), ',')) AS single_email
WHERE TRIM(single_email) != '';

MySQL 8.0+ #

WITH RECURSIVE email_split AS (
    SELECT
        `Header 1`,
        `Header 2`,
        REGEXP_REPLACE(`Header 3`, '[<>]', '') AS email_str,
        1 AS pos
    FROM 你的表名
    UNION ALL
    SELECT
        `Header 1`,
        `Header 2`,
        email_str,
        pos + 1
    FROM email_split
    WHERE pos <= LENGTH(email_str) - LENGTH(REPLACE(email_str, ',', '')) + 1
)
SELECT
    `Header 1`,
    `Header 2`,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(email_str, ',', pos), ',', -1)) AS `Header 3`
FROM email_split
WHERE TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(email_str, ',', pos), ',', -1)) != '';

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