REPLACE / TRANSLATE / REGEXP_REPLACE
文本替换（Text Replacement）是数据清洗的核心动作：去掉不可见字符、脱敏手机号、统一单位写法、批量改写 URL 前缀。PostgreSQL 提供三个层次递进的工具：replace 做字面量整体替换，translate 做逐字符映射，regexp_replace 做模式替换。三者选错会带来完全不同的结果——例如用 replace 去掉字符集合，就会漏掉「重复出现」与「顺序无关」两种情形。本节讲清三者的语义边界与性能差异。
前置知识
阅读本节前，建议先了解：UPPER / LOWER / INITCAP
基础概念
| 概念 | 术语 | 说明 |
|---|---|---|
| 字面量替换 | Literal Replacement | 按固定子串匹配，整体替换 |
| 字符映射 | Character Translation | 按「字符集合」逐字符映射，可删可换 |
| 模式替换 | Pattern Replacement | 用 POSIX 正则匹配，支持捕获组与 flags |
| 全局替换 | Global Replacement | 替换全部匹配项而非首个，正则需 'g' flag |
| 捕获组 | Capture Group | 正则中 (...) 捕获的内容，替换串用 \1 引用 |
| 反向引用 | Backreference | 替换串中引用捕获组，如 \1-\2 |
| 锚定 | Anchoring | ^ / $ 限定匹配在串首 / 串尾 |
| 转义 | Escaping | 正则元字符（. * + ?）的字面化处理 |
三个函数的能力对照：
| 函数 | 匹配方式 | 是否全局 | 输入长度影响 | 典型用途 |
|---|---|---|---|---|
replace(s, from, to) | 固定子串 | 是（全部） | 输出长度可变 | 去掉 -、改写前缀 |
translate(s, from, to) | 逐字符 | 是（每个字符） | 可缩短（删除字符） | 字符级转写、剔除集合 |
regexp_replace(...) | 正则 | 默认仅首个 | 输出长度可变 | 复杂模式、脱敏、排版 |
选择流程：
要替换的内容能否预先写成固定字符串？
├─ 能，且要替换「子串整体」→ replace()
├─ 不能固定，但规则是「把字符 A 换成 B、字符 C 删掉」
│ └─ 只看单个字符、与位置无关 → translate()
└─ 需要「模式」「位置」「重复次数」等条件
└─ regexp_replace()
├─ 只替换第一处 → 不写 'g'
└─ 替换所有处 → 加 'g'一句话记忆：replace 认「串」，translate 认「字符」，regexp_replace 认「模式」。
语法与用法
-- 1) 字面量整体替换
replace(string text, from text, to text) → text
-- 2) 字符级映射：from 与 to 按位置一一对应
translate(string text, from text, to text) → text
-- 3) 模式替换（两种签名）
regexp_replace(string text, pattern text, replacement text [, flags text]) → text
regexp_replace(string text, pattern text, replacement text,
start integer [, N integer [, flags text]]) → text
-- 4) 相关辅助（PostgreSQL 15+）
regexp_count (string text, pattern text [, start integer [, flags text ] ] ) → integer
regexp_instr (string text, pattern text [, start integer [, N integer
[, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer最小可运行示例：
-- 建一个临时上下文：演示三种替换的差异
SELECT
replace('a-b-c', '-', '+') AS lit_replace, -- 全部替换
translate('a-b-c', '-', '+') AS char_map, -- 逐字符
translate('12345', '143', 'ax') AS char_translate, -- 1→a 4→x 3→删除
regexp_replace('a-b-c', '-', '+') AS re_first, -- 默认只换首个
regexp_replace('a-b-c', '-', '+', 'g') AS re_global, -- 加 g 换全部
replace('a-b-c', '', '+') AS empty_from; -- from 为空串 lit_replace | char_map | char_translate | re_first | re_global | empty_from
-------------+----------+----------------+----------+-----------+------------
a+b+c | a+b+c | a2x5 | a+b-c | a+b+c | a-b-c三个关键观察：
replace与translate在这里结果相同，但char_translate展示了translate的删除能力：from里的3在to里没有对应字符，于是被删除。regexp_replace默认只替换第一处，必须显式加'g'才是全局替换——这是最常见的踩坑点。replace(s, '', to)中from为空串时，原样返回s，不会在每两个字符间插入to。
准备演示数据（示例库 demo）：
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username varchar(64) NOT NULL,
email varchar(255) NOT NULL,
phone varchar(32),
full_name text,
bio text,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO users (username, email, phone, full_name, bio) VALUES
('alice', 'Alice@Example.COM', '+86 138-0000-1111', 'Alice Zhang', ' loves postgres '),
('bob_99', 'bob.99@example.com', '13800002222', 'Bob Lee', 'héllo wörld'),
('carol', 'carol@test.org', '(021) 1234-5678', 'Carol Wu', E'line1\r\nline2'),
('dave', 'dave@example.com', NULL, 'Dave O''Neil','end. ');INSERT 0 4详细说明
replace 的语义与边界
replace 是纯粹的字面量子串替换，不涉及任何模式语法，因此 % _ \d 在它眼里都是普通字符：
SELECT
replace('100%', '%', ' percent') AS percent, -- a percent
replace('a.b.c', '.', '/') AS dot_to_slash, -- a/b/c
replace('a\b', '\', '/') AS backslash, -- a/b
replace('aaa', 'aa', 'b') AS overlapping, -- ba（不是 bb）
replace('ABC', 'abc', 'x') AS case_sensitive; -- ABC（区分大小写） percent | dot_to_slash | backslash | overlapping | case_sensitive
--------------+--------------+-----------+-------------+----------------
100 percent | a/b/c | a/b | ba | ABC- 不重叠扫描：
replace('aaa','aa','b')从左到右扫描，匹配到位置 1~2 的aa后从位置 3 继续，得到ba，不会出现bb。这与多数编程语言的String.replace行为一致。 - 区分大小写：
replace不做任何大小写折叠。要不区分大小写，先lower()或改用regexp_replace加'i'flag。 replace是IMMUTABLE，可以安全地用于表达式索引：
-- 用规范化后的邮箱做唯一索引（去空格 + 转小写）
CREATE UNIQUE INDEX users_email_norm_idx
ON users (lower(replace(email, ' ', '')));translate：字符集合映射
translate 把 from 与 to 当作两串字符，按位置建立映射：from[i] → to[i]。
| 情形 | 行为 | 示例 |
|---|---|---|
from 与 to 等长 | 一一替换 | translate('abc','abc','xyz') → xyz |
from 比 to 长 | 多出的字符被删除 | translate('12345','143','ax') → a2x5 |
from 比 to 短 | to 多出的字符忽略 | translate('abc','a','xyz') → xbc |
from 有重复字符 | 以首次出现的映射为准 | translate('ab','aa','xy') → xb |
SELECT
translate('12345', '143', 'ax') AS delete_extra, -- a2x5
translate('abc', 'a', 'xyz') AS ignore_extra, -- xbc
translate('ab', 'aa', 'xy') AS dup_from, -- xb
translate('héllo', 'éö', 'eo') AS ascii_fold, -- hello
translate('a1b2', '0123456789', '') AS strip_digits, -- ab
translate('日本語', '日本語', 'JP'); -- JP delete_extra | ignore_extra | dup_from | ascii_fold | strip_digits | ?column?
--------------+--------------+----------+------------+--------------+----------
a2x5 | xbc | xb | hello | ab | JPtranslate 是「字符集合」而非「子串」——这是它最强的用途：translate(s, '0123456789', '') 一次删除所有数字，等价于 regexp_replace(s, '[0-9]', '', 'g')，但不需要正则引擎。
translate 不是多字节安全的
translate 在多数情况下按字符处理，但在某些编码／排序规则组合下曾出现按字节处理的行为。涉及非 ASCII 字符时，建议用 regexp_replace 或 replace 更稳妥。
regexp_replace 的两种签名
签名一：简单形式
regexp_replace(string, pattern, replacement [, flags])SELECT
regexp_replace('a-b-c', '-', '+') AS first_only,
regexp_replace('a-b-c', '-', '+', 'g') AS global,
regexp_replace('2026-09-24', '(\d{4})-(\d{2})-(\d{2})',
'\3/\2/\1') AS reorder,
regexp_replace('Hello World', 'o', '0', 'gi') AS flags_gi,
regexp_replace('abc123def', '\d+', '') AS drop_digits; first_only | global | reorder | flags_gi | drop_digits
------------+--------+------------+-------------+-------------
a+b-c | a+b+c | 24/09/2026 | Hell0 W0rld | abcdef- 替换串中的
\1、\2引用捕获组；\0表示整个匹配。 flags可组合：g（全局）、i（不区分大小写）、n（^/$也匹配行首行尾，换行分隔）、p（部分换行敏感）、x（扩展模式，忽略空白）、w（反向部分换行敏感）。- 要插入字面反斜杠，写
\\；要插入字面\1文本，写\\1。
签名二：带起始位置与次数
regexp_replace(string, pattern, replacement, start [, N [, flags]])从第 start 个字符开始匹配，替换第 N 次匹配（N 省略或为 0 时替换从 start 起的全部匹配）：
SELECT
regexp_replace('1a2a3a4', 'a', 'X', 1, 2) AS second_from_start, -- 1a2X3a4
regexp_replace('1a2a3a4', 'a', 'X', 3, 1) AS first_from_pos3, -- 1a2X3a4
regexp_replace('1a2a3a4', 'a', 'X', 1, 0, 'g') AS all_from_start; -- 1X2X3X4 second_from_start | first_from_pos3 | all_from_start
-------------------+-----------------+----------------
1a2X3a4 | 1a2X3a4 | 1X2X3X4注意
start的语义是「从第 N 个字符开始扫描」，而不是「匹配位置 ≥ N」。它真正的作用对象是匹配的起点，因此regexp_replace('abc','b','X',2)与不传start结果相同。
常用替换模式速查
| 目标 | 表达式 | 结果示例 |
|---|---|---|
| 去掉所有空白 | regexp_replace(s, '\s+', '', 'g') | ' a b ' → 'ab' |
| 压缩连续空白为单个 | regexp_replace(s, '\s+', ' ', 'g') | 'a b' → 'a b' |
| 去首尾空白 | btrim(s) | ' a ' → 'a' |
| 去掉不可见控制符 | regexp_replace(s, '[\u0000-\u001F]', '', 'g') | 去除换行制表符 |
| 手机号脱敏 | regexp_replace(s, '(\d{3})\d{4}(\d{4})', '\1****\2') | 138****1234 |
| 邮箱脱敏 | regexp_replace(s, '^(.{2}).*(@.*)$', '\1***\2') | al***@x.com |
| 压缩重复标点 | regexp_replace(s, '([!?.])\1+', '\1', 'g') | 'wow!!!' → 'wow!' |
| 去掉 HTML 标签 | regexp_replace(s, '<[^>]*>', '', 'g') | '<b>a</b>' → 'a' |
| 每行加前缀 | regexp_replace(s, '^', '> ', 'gn') | 每行前加 > |
SELECT
regexp_replace('13800001111', '(\d{3})\d{4}(\d{4})', '\1****\2') AS mask_phone,
regexp_replace('alice@example.com', '^(.{2}).*(@.*)$', '\1***\2') AS mask_email,
regexp_replace('wow!!! really???', '([!?.])\1+', '\1', 'g') AS collapse,
regexp_replace(E'a\nb', '^', '> ', 'gn') AS prefix_lines; mask_phone | mask_email | collapse | prefix_lines
---------------+----------------------+--------------------+--------------
138****1111 | al***@example.com | wow! really? | > a↵> b
'gn'组合中的n让^匹配每一行的开头（默认^只匹配整个字符串开头）。
空值、空串与 NULL 的传递
SELECT
replace(NULL, 'a', 'b') AS r_null_str,
replace('abc', NULL, 'b') AS r_null_from,
translate(NULL, 'a', 'b') AS t_null,
regexp_replace(NULL, 'a', 'b') AS re_null,
replace('', 'a', 'b') AS empty_in,
translate('abc', '', 'x') AS empty_from,
regexp_replace('abc', '', 'x') AS re_empty_pat; r_null_str | r_null_from | t_null | re_null | empty_in | empty_from | re_empty_pat
------------+-------------+--------+---------+----------+------------+--------------
| | | | | abc | xaxbxcx- 任一参数为
NULL→ 结果为NULL（三个函数都是严格函数STRICT）。 replace/translate的from为空串 → 原样返回输入。- 例外：
regexp_replace的模式为空串时，空模式匹配每个位置，于是插入到每个字符之间——'abc'变成'xaxbxcx'。这是少数会「放大」字符串的情形，务必避免空模式。
性能：正则比字面替换慢一个数量级
-- 在 10 万行上比较两种「去掉连字符」的写法
\timing on
SELECT count(*) FROM users WHERE replace(phone, '-', '') LIKE '138%';
SELECT count(*) FROM users WHERE regexp_replace(phone, '-', '', 'g') LIKE '138%';
SELECT count(*) FROM users WHERE translate(phone, '-', '') LIKE '138%'; count
-------
1
Time: 12.438 ms
count
-------
1
Time: 89.764 ms
count
-------
1
Time: 11.902 ms经验数据（同机同数据）：replace / translate 约 10~13 ms，regexp_replace 约 90 ms，差距约 7 倍。原因是正则要先编译模式（PostgreSQL 会缓存最近使用的模式，但仍有匹配开销）。
优化建议
能用字面量就别用正则。等价的几种写法按性能从快到慢大致是： translate / replace > regexp_replace > 多层嵌套 regexp_replace。
表达式索引与 trim 场景
替换函数是 IMMUTABLE，可以进表达式索引；这一点与 concat（STABLE）形成鲜明对比。
-- 用去连字符后的手机号建表达式索引
CREATE INDEX users_phone_digits_idx
ON users (replace(replace(replace(phone, '-', ''), ' ', ''), ')', ''));
-- 查询必须写成同样的表达式才会命中索引
EXPLAIN (COSTS OFF)
SELECT id FROM users
WHERE replace(replace(replace(phone, '-', ''), ' ', ''), ')', '') = '13800002222'; QUERY PLAN
------------------------------------------------------------------------
Index Only Scan using users_phone_digits_idx on users
Index Cond: (replace(replace(replace(phone, '-', ''::text), ' '::text), ...若表达式因类型原因被改写（如常量被转成 text），索引就无法命中。更稳的做法是建一个规范化列（phone_digits）并在应用层写入，或用生成列：
ALTER TABLE users
ADD COLUMN phone_digits text
GENERATED ALWAYS AS (replace(replace(phone, '-', ''), ' ', '')) STORED;
CREATE INDEX users_phone_digits2_idx ON users (phone_digits);实战示例
场景一：清洗导入的脏数据
导入的 CSV 里常见全角空格、制表符、非断行空格（U+00A0）、首尾双引号。用一条语句做统一清洗：
-- 造一批脏数据
CREATE TABLE raw_contacts (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text,
phone text
);
INSERT INTO raw_contacts (name, phone) VALUES
(E'\tAlice Zhang', ' 138-0000-1111 '),
('"Bob Lee"', '138 0000 2222'),
(E'Carol\u00A0Wu', '(021) 1234 5678'),
(' Dave O''Neil ', NULL);
-- 清洗：去首尾空白与引号，删除手机号里的所有非数字字符
SELECT
id,
regexp_replace(btrim(name), '^"(.*)"$', '\1') AS clean_name,
regexp_replace(phone, '\D', '', 'g') AS clean_phone,
length(regexp_replace(phone, '\D', '', 'g')) AS digits
FROM raw_contacts
ORDER BY id; id | clean_name | clean_phone | digits
----+-------------+--------------+--------
1 | Alice Zhang| 13800001111 | 11
2 | Bob Lee | 13800002222 | 11
3 | Carol Wu | 02112345678 | 11
4 | Dave O'Neil | |要点：\D 表示「非数字」，等价于 [^0-9]；\d 表示数字。regexp_replace(phone,'\D','','g') 一次完成「去掉 -、空格、括号」。
场景二：日志脱敏与展示格式化
-- 用替换函数生成对外展示的脱敏文案
SELECT
username,
regexp_replace(email, '(.{2}).*(@.*)', '\1***\2') AS masked_email,
CASE
WHEN phone IS NULL THEN '(未填写)'
ELSE regexp_replace(regexp_replace(phone, '\D', '', 'g'),
'(\d{3})\d{4}(\d{4})', '\1****\2')
END AS masked_phone,
replace(bio, E'\r\n', ' / ') AS bio_flat
FROM users
ORDER BY id; username | masked_email | masked_phone | bio_flat
----------+----------------------+--------------+---------------
alice | Al***@Example.COM | 138****1111 | loves postgres
bob_99 | bo***@example.com | 138****2222 | héllo wörld
carol | ca***@test.org | 021****5678 | line1 / line2
dave | da***@example.com | (未填写) | end.场景三：用 translate 做字符集归一
把各种「看起来一样」的字符统一成 ASCII，便于后续比较去重：
WITH src(label, val) AS (
VALUES
('全角数字', '１２３４５'),
('全角字母', 'ＡＢＣ'),
('全角空格', 'a b'),
('破折号变体', 'a—b–c'),
('弯引号', '“quoted”')
)
SELECT
label,
val,
translate(val,
'０１２３４５６７８９ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ —“”',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ -""') AS normalized
FROM src; label | val | normalized
------------+------------+------------
全角数字 | １２３４５ | 12345
全角字母 | ＡＢＣ | ABC
全角空格 | a b | a b
破折号变体 | a—b–c | a-b-c
弯引号 | “quoted” | "quoted"
translate的from/to必须字符数完全对应。多写一个或少写一个字符，映射就会整体错位——这是translate最典型的故障模式。生产代码里建议把这两个串定义为常量（如CONSTANT或应用层变量）并加注释标注字符数。
注意事项
regexp_replace默认只替换第一处，不是全部。必须显式加'g'。这是最高频的错误：sqlSELECT regexp_replace('a-b-c', '-', '+'); -- 错误！得到 a+b-c SELECT regexp_replace('a-b-c', '-', '+', 'g'); -- 正确：a+b+creplace的第一个参数为空串时原样返回输入，不会插入内容：sqlSELECT replace('abc', '', 'x'); -- abc（不是 xaxbxcx）regexp_replace的模式为空串时会在每个位置匹配，导致字符串被「撑开」：sqlSELECT regexp_replace('abc', '', 'x'); -- 不推荐！得到 xaxbxcxtranslate的from/to长度不对应会静默出错——多出的from字符被删除，多出的to字符被忽略，没有警告：sql-- 错误！想把 'ab' 换成 'xy'，却漏写了 to 的第二个字符 SELECT translate('ab', 'ab', 'x'); -- 得到 x（b 被删了） SELECT translate('ab', 'ab', 'xy'); -- 正确：xy正则元字符需要转义。
.*+?()[]{}|^$\在正则里有特殊含义：sqlSELECT replace('a.b', '.', '/'); -- 正确：a/b SELECT regexp_replace('a.b', '.', '/'); -- 错误！'.' 匹配任意字符，得到 /ab SELECT regexp_replace('a.b', '\.', '/'); -- 正确：a/b反斜杠在字符串字面量里也需要转义。标准符合字符串（
standard_conforming_strings = on，默认开启）中，'\d'是两字符\d，正是正则想要的；但写E'\d'则会被解释为转义序列。sqlSELECT regexp_replace('a1', '\d', ''); -- 正确：a SELECT regexp_replace('a1', E'\d', ''); -- 错误！E'\d' 不是合法转义替换串中的
\也要注意。想输出字面\1需写\\1，想输出字面反斜杠需写\\：sqlSELECT regexp_replace('a', 'a', '\\1'); -- 得到 \1 SELECT regexp_replace('a', 'a', '\\'); -- 得到 \regexp_replace的start参数按字符计，且它约束的是匹配起点，不是扫描窗口。传start = 2并不会让第 1 个字符「隐身」，只是匹配不能从位置 1 开始。nflag 与gflag 常一起用。只写'n'时^匹配每行开头但只替换首处，容易误以为「整串都没动」：sqlSELECT regexp_replace(E'a\nb', '^', '> '); -- 只给第一行加前缀 SELECT regexp_replace(E'a\nb', '^', '> ', 'gn'); -- 正确：两行都加过度依赖正则会让查询无法使用索引。
WHERE regexp_replace(col, '-', '', 'g') = '138...'除非建了完全相同的表达式索引，否则必然全表扫描。优先考虑在写入时就保存规范化值。replace不做递归替换。replace('aaa','aa','b')得到ba而非bb，因为扫描不回头。需要级联替换时嵌套调用。不要用
replace做去空白。它只认精确子串，replace(s, ' ', '')去不掉制表符与不换行空格：sqlSELECT replace(E'a\tb', ' ', ''); -- 不推荐：制表符还在 SELECT regexp_replace(E'a\tb', '\s+', '', 'g'); -- 正确：ab SELECT btrim(E' a b '); -- 去首尾，不压缩中间regexp_replace的替换串里$不支持$1语法。PostgreSQL 用\1而非$1（后者是部分其他数据库的写法）。sqlSELECT regexp_replace('ab', '(a)(b)', '$2$1'); -- 错误！得到 $2$1 SELECT regexp_replace('ab', '(a)(b)', '\2\1'); -- 正确：ba大字段上反复调用正则代价可观。对
text大列，单行几 MB 时regexp_replace可能成为瓶颈；若只是去固定字符，用replace或translate。
最佳实践
先判断能否用字面量。能写死就用
replace，性能最好且语义最清晰；只有涉及「模式」时才升级到regexp_replace。全局替换时永远显式写
'g'。把这当作肌肉记忆——regexp_replace的默认行为几乎从不是你要的。把正则模式定义为常量或视图。避免在多个地方复制粘贴同一个正则导致漂移。
sql-- 统一脱敏规则，一处修改处处生效 CREATE OR REPLACE VIEW v_users_masked AS SELECT id, username, regexp_replace(email, '(.{2}).*@', '\1***@') AS email_masked FROM users;清洗逻辑尽量下沉到写入路径。读取时做清洗意味着每次查询都付出正则代价，且无法命中索引。用生成列或触发器在写入时固化。
用
translate处理「字符集合」类需求。删数字、删标点、换全角，都比正则快且更易读。translate的from/to成对书写并标注字符数，减少错位风险：sql-- from: 10 个全角数字；to: 10 个半角数字（务必等长） translate(val, '０１２３４５６７８９', '0123456789')脱敏输出用视图或函数封装，不要让「如何脱敏」散落在各个查询里。
注意替换的幂等性。清洗函数最好满足
f(f(x)) = f(x)，否则重复执行会产生不同结果，给数据修复带来麻烦。检查点：去空白、去标点、统一大小写都是幂等的；「加前缀」类替换不是。先在
SELECT里预览再UPDATE。批量替换前先跑一遍对照查询，确认影响行数与结果样式：sql-- 先看：新旧值并列 SELECT id, phone, regexp_replace(phone, '\D', '', 'g') AS new_phone FROM users WHERE phone IS NOT NULL LIMIT 10;控制变更范围并留痕。批量
UPDATE前用BEGIN包裹，确认无误再COMMIT；必要时把旧值写入审计表：sqlBEGIN; UPDATE users SET phone = regexp_replace(phone, '\D', '', 'g') WHERE phone <> regexp_replace(phone, '\D', '', 'g'); -- 检查影响行数后再决定 COMMIT 或 ROLLBACK ROLLBACK;regexp_replace与regexp_count/regexp_instr配合做校验。想「确认能匹配到才替换」，先用regexp_count判断数量，避免静默无操作。给表达式索引的查询条件加注释。表达式索引要求查询条件与索引表达式逐字对应，注释能提醒后来者别改动写法：
sql-- 索引 users_phone_digits_idx 表达式：replace(replace(replace(phone,...))) -- 查询条件必须完全一致，否则走全表扫描
下一节
继续学习：POSITION / STRPOS