如何在PostgreSQL中使用REGEXP_REPLACE实现邮箱格式替换

问题背景 #

现有存储邮箱地址的contacts表，结构及初始化数据如下：

CREATE TABLE contacts(
    email     VARCHAR(255)
)

INSERT INTO contacts VALUES    
    ('example.person@gmail.com'),
    ('example.person2@gmail.com'),
    ('example.person3@gmail.com');

需要将邮箱格式从example.person@gmail.com转换为example.person_gmailcom@test.com，但执行以下语句后得到错误结果example.person@test.comgmail.com：

UPDATE contacts
SET email = REGEXP_REPLACE(email, '@', '@test.com');

错误原因 #

上述语句仅将@替换为@test.com，并未移除原邮箱中@后的域名部分（如gmail.com），导致原域名被保留在新后缀之后，出现拼接错误。

正确实现方式 #

方法1：字符串函数拆分拼接 #

通过拆分邮箱的用户名和域名部分，移除域名中的.后重新拼接：

UPDATE contacts
SET email = CONCAT(
  -- 提取@之前的用户名部分
  LEFT(email, POSITION('@' IN email) - 1),
  '_',
  -- 提取@之后的域名部分并移除所有点
  REPLACE(RIGHT(email, LENGTH(email) - POSITION('@' IN email)), '.', ''),
  '@test.com'
);

方法2：正则表达式捕获组 #

利用正则捕获用户名和域名，结合替换处理域名中的点：

UPDATE contacts
SET email = REGEXP_REPLACE(
  email,
  '^(.*)@(.*)$',
  '\1_' || REPLACE('\2', '.', '') || '@test.com'
);

执行上述任一语句后，邮箱将被正确转换为example.person_gmailcom@test.com格式。

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