PostgreSQL拆分多邮箱为用户名、域名、后缀列并实现插入

1. 拆分单个/多个邮箱地址 #

假设你有一张存储邮箱的表email_list，包含email列，要拆分出用户名、域名、后缀，可以用PostgreSQL的字符串函数组合实现：

SELECT
  -- 提取@前的部分，把点替换为空格
  REPLACE(SPLIT_PART(email, '@', 1), '.', ' ') AS username,
  -- 提取@后的部分，拆分出域名（第一个点前的内容）
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 1) AS domain,
  -- 提取@后的部分，拆分出后缀（第一个点后的内容）
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 2) AS extension
FROM email_list;

函数说明 #

• SPLIT_PART(str, delimiter, n)：按指定分隔符拆分字符串，返回第n个部分
• REPLACE(str, old, new)：替换字符串中的指定字符

比如处理robert.bryne@gmail.com时，会返回：

如果需要处理临时邮箱列表（不从现有表读取），可以用VALUES子句：

SELECT
  REPLACE(SPLIT_PART(email, '@', 1), '.', ' ') AS username,
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 1) AS domain,
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 2) AS extension
FROM (
  VALUES 
    ('robert.bryne@gmail.com'),
    ('john.smith@outlook.com'),
    ('jane.doe@yahoo.co.uk')
) AS temp(email);

过滤无效邮箱 #

为避免格式错误的邮箱干扰结果，可添加正则过滤条件：

SELECT
  REPLACE(SPLIT_PART(email, '@', 1), '.', ' ') AS username,
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 1) AS domain,
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 2) AS extension
FROM email_list
WHERE
  email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

2. 将结果插入目标表 #

先创建目标表（如果还不存在）：

CREATE TABLE email_details (
  username TEXT,
  domain TEXT,
  extension TEXT
);

使用INSERT INTO ... SELECT ...语法直接插入拆分结果：

INSERT INTO email_details (username, domain, extension)
SELECT
  REPLACE(SPLIT_PART(email, '@', 1), '.', ' ') AS username,
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 1) AS domain,
  SPLIT_PART(SPLIT_PART(email, '@', 2), '.', 2) AS extension
FROM email_list
WHERE
  email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'; -- 可选：仅插入有效邮箱

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