SUBSTRING / LEFT / RIGHT / TRIM
截取（Extraction）与修剪（Trimming）是字符串清洗中最常用的两类操作。PostgreSQL 的截取家族包含 SQL 标准的 substring、便捷的 left / right，以及带正则能力的 substring(... FROM pattern) 变体；修剪家族则包含 SQL 标准的 trim 与 PostgreSQL 扩展的 ltrim / rtrim / btrim。本节将厘清起始位置从 1 开始、负数下标、正则变体这些高频踩坑点。
前置知识
阅读本节前，建议先了解：CONCAT / || 拼接
基础概念
| 概念 | 术语 | 说明 |
|---|---|---|
| 子串 | Substring | 原字符串中连续的一段 |
| 起始位置 | Start Position | PostgreSQL 中从 1 开始，0 视同 1 |
| 长度 | Count / Length | 要取的字符数，省略则取到末尾 |
| 左截取 | Left | 从头部取 n 个字符 |
| 右截取 | Right | 从尾部取 n 个字符 |
| 修剪 | Trim | 去掉首尾的指定字符，默认是空格 |
| 字符集修剪 | Character Set Trim | 按「字符集合」而非「子串」删除 |
| 正则变体 | POSIX Variant | substring(str FROM pattern) 的匹配模式 |
| 捕获组 | Capture Group | 正则中括号内的部分，substring 只返回第一个 |
四类操作的定位对照：
| 函数 | 定位方式 | 负数参数含义 | 边界越界行为 |
|---|---|---|---|
substring(str FROM s FOR n) | 起止区间 | s 为负则整体左移，前补空 | 超出部分忽略 |
left(str, n) | 从左计数 | 返回「除最后 |n| 个字符」的全部 | n 超长返回整串 |
right(str, n) | 从右计数 | 返回「除最前 |n| 个字符」的全部 | n 超长返回整串 |
trim(...) | 首尾字符集合 | 不适用 | 全为待删字符则返回空串 |
截取的三套语法并存，选择依据是「你要的是位置还是模式」：
text
你要截取的内容如何描述？
├─ 按固定位置与长度 → substring(str FROM s FOR n) 或 substr(str, s, n)
├─ 按「前 n 个 / 后 n 个」→ left(str, n) / right(str, n)
└─ 按「符合某个模式的部分」→ substring(str FROM '<正则>')语法与用法
sql
-- 1. SQL 标准：起始位置 + 长度
substring(str FROM start)
substring(str FROM start FOR count)
substring(str, start) -- 逗号写法，等价
substring(str, start, count) -- 逗号写法，等价
substr(str, start [, count]) -- 老式别名，功能相同
-- 2. POSIX 正则形式
substring(str FROM pattern) -- 返回第一个匹配
substring(str FROM pattern FOR escape) -- SQL:2003 风格，POSIX 中走 SIMILAR
-- 3. 左右截取
left(str, n)
right(str, n)
-- 4. 修剪
trim([LEADING | TRAILING | BOTH] [chars] FROM str)
trim(str) -- 简写：等价于 btrim(str)
btrim(str [, chars]) -- 去首尾
ltrim(str [, chars]) -- 去头部
rtrim(str [, chars]) -- 去尾部最小可运行示例：
sql
SELECT
substring('PostgreSQL 18' FROM 1 FOR 4) AS sub_std, -- 从第 1 位取 4 个
substring('PostgreSQL 18' FROM 11) AS sub_to_end, -- 从第 11 位取到末尾
substr('PostgreSQL 18', 6, 3) AS sub_alias, -- 老式别名
left('PostgreSQL 18', 4) AS l4,
right('PostgreSQL 18', 2) AS r2,
btrim(' demo ') AS trimmed;text
sub_std | sub_to_end | sub_alias | l4 | r2 | trimmed
---------+------------+-----------+------+----+---------
Post | 18 | SQL | Post | 18 | demo准备本节贯穿使用的示例表：
sql
CREATE TABLE products (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL, -- 形如 'CN-ELEC-20260001'
name TEXT NOT NULL, -- 可能带首尾空格
category TEXT NOT NULL,
batch_no TEXT, -- 形如 ' B20260922-01 '
price NUMERIC(10, 2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO products (sku, name, category, batch_no, price) VALUES
('CN-ELEC-20260001', ' 无线鼠标 ', 'elec', ' B20260922-01 ', 129.00),
('CN-ELEC-20260002', '机械键盘', 'elec', 'B20260922-02', 499.00),
('CN-BOOK-20260001', 'PostgreSQL 指南', 'book', NULL, 89.50),
('US-BOOK-20260002', 'SQL 进阶', 'book', ' B20260923-07 ', 69.00),
('CN-FOOD-20260001', '咖啡豆', 'food', '', 58.00);详细说明
substring 的位置语义
起点从 1 开始，这是与多数编程语言（从 0 开始）最大的差异：
sql
SELECT
substring('abcdef' FROM 1 FOR 3) AS s1, -- abc
substring('abcdef' FROM 2 FOR 3) AS s2, -- bcd
substring('abcdef' FROM 0 FOR 3) AS s0, -- 0 视同 1
substring('abcdef' FROM -2 FOR 5) AS sneg, -- 起点左移，前面补空
substring('abcdef' FROM 10 FOR 3) AS soob; -- 越界，返回空串text
s1 | s2 | s0 | sneg | soob
------+------+------+------+------
abc | bcd | abc | ab |start 为负是最反直觉的一处：substring('abcdef' FROM -2 FOR 5) 表示窗口从「第 -2 位」开始取 5 个字符，即 [-2, -1, 0, 1, 2]，其中只有位置 1、2 存在，因此得到 ab。
用等价写法理解更清晰：
sql
-- 负起始位置的等价推导（仅用于理解，不要在业务里这样写）
-- 窗口 = [start, start + count - 1]
-- start = -2, count = 5 → 窗口 = [-2, 2] → 实际存在的只有 [1, 2] → 'ab'
SELECT substring('abcdef' FROM -2 FOR 5) AS explained;text
explained
-----------
absubstring 的省略与越界
省略 FOR 时取到字符串末尾；count 为负会报错：
sql
SELECT
substring('abcdef' FROM 4) AS no_count, -- def
substring('abcdef' FROM 4 FOR 100) AS oversized, -- def，超出部分忽略
substring('abcdef' FROM 100) AS beyond; -- 空串text
no_count | oversized | beyond
----------+-----------+--------
def | def |sql
-- 错误！count 不能为负
SELECT substring('abcdef' FROM 2 FOR -1);text
ERROR: negative substring length not allowed对 NULL 输入的行为遵循标准 SQL 的传染规则：
sql
SELECT
substring(NULL FROM 1 FOR 2) AS null_str,
substring('abc' FROM NULL FOR 2) AS null_start;text
null_str | null_start
----------+------------
|left 与 right：负数下标的意义
left / right 的 n 为负时，语义是「去掉另一端」：
sql
SELECT
left('abcdef', 3) AS l3, -- abc
left('abcdef', -2) AS l_neg, -- 去掉最后 2 个 → abcd
right('abcdef', 3) AS r3, -- def
right('abcdef', -2) AS r_neg, -- 去掉最前 2 个 → cdef
left('abcdef', 0) AS l0, -- 空串
right('abcdef', 0) AS r0, -- 空串
left('abcdef', 100) AS l_over, -- 整串
left('abcdef', -100) AS l_over_neg;-- 空串text
l3 | l_neg | r3 | r_neg | l0 | r0 | l_over | l_over_neg
------+-------+------+-------+----+----+--------+------------
abc | abcd | def | cdef | | | abcdef |left(str, -2) 等价于 substring(str FROM 1 FOR length(str) - 2)，前者写法更清晰。
与 substring 一样，left / right 按字符而非字节计数，多字节字符不会被切坏：
sql
SELECT
left('中国数据库', 3) AS cn_left,
right('中国数据库', 2) AS cn_right,
octet_length(left('中国数据库', 3)) AS byte_len;text
cn_left | cn_right | byte_len
---------+----------+----------
中国数 | 据库 | 9left('中国数据库', 3) 得到 3 个汉字、9 字节，字符边界完整。
trim 家族：按字符集合删除
trim 有一个极易误解的地方：chars 参数是「字符集合」而非「待删除的子串」。它会把首尾所有出现在集合中的字符逐个删掉。
sql
SELECT
trim(BOTH 'x' FROM 'xxHelloxx') AS both_trim, -- 去首尾 x
trim(LEADING 'x' FROM 'xxHelloxx') AS lead_trim, -- 只去头
trim(TRAILING 'x' FROM 'xxHelloxx') AS trail_trim, -- 只去尾
trim(' demo ') AS default_trim; -- 默认为 BOTH + 空格text
both_trim | lead_trim | trail_trim | default_trim
-----------+-----------+------------+--------------
Hello | Helloxx | xxHello | demo集合语义的证明：下面的例子中 'xyz' 是三个字符的集合，xxxHelloxyz 的首尾所有 x / y / z 都被删掉：
sql
SELECT
btrim('xxxHelloxyz', 'xyz') AS set_trim, -- Hello
btrim('Hello', 'xyz') AS no_match, -- Hello
btrim('HHHelloHHH', 'H') AS repeat_trim, -- ello
btrim('abc', 'abcdef') AS all_removed, -- 集合覆盖全部字符 → 空串
btrim('', 'abc') AS empty_in; -- 空串text
set_trim | no_match | repeat_trim | all_removed | empty_in
----------+----------+-------------+-------------+----------
Hello | Hello | ello | |注意 btrim('abc', 'abcdef') 返回空串——因为 a / b / c 都在集合里，被全部删除。若你的本意是「删掉前缀 abcdef」，trim 做不到，得用 regexp_replace 或 substring。
sql
-- 想删除「特定子串」而不是「字符集合」，用 replace 或正则
SELECT
replace('abcdefabc', 'abc', '') AS del_substr, -- def
regexp_replace('abcdefabc', '^(abc)+', '') AS del_prefix_re, -- defabc
regexp_replace('abcdefabc', '^abc', '') AS del_prefix_once; -- defabctext
del_substr | del_prefix_re | del_prefix_once
------------+---------------+-----------------
def | def | defabc空值、空串与 NULL 的三方区分
清洗数据时最需要区分「空格串」「空串」「NULL」：
sql
SELECT
trim(' ') AS spaces_to_null, -- 空格被删，结果为空串
length(trim(' ')) AS len_after, -- 0
length(' ') AS len_before, -- 3
trim('') AS empty_in, -- 空串
trim(NULL) AS null_in, -- NULL
nullif(trim(' '), '') AS normalized; -- 空串转 NULL（推荐）text
spaces_to_null | len_after | len_before | empty_in | null_in | normalized
----------------+-----------+------------+----------+---------+------------
| 0 | 3 | | |trim(' ') 返回空串而不是 NULL。若业务语义上「全空格」应视作「无值」，必须显式用 nullif(trim(x), '')——这是数据清洗的标准收尾动作：
sql
-- 把清洗结果落库，统一空值语义
UPDATE products
SET name = nullif(btrim(name), '')
WHERE name IS DISTINCT FROM nullif(btrim(name), '');正则变体：substring(str FROM pattern)
当截取位置不固定时，substring 的正则形式是利器。规则有三条：
- 返回第一个匹配的子串；
- 若模式中包含捕获组（括号），返回第一个捕获组的内容；
- 若没有括号，返回整个匹配。
sql
SELECT
substring('CN-ELEC-20260001' FROM '[0-9]+') AS no_group, -- 第一个连续数字
substring('CN-ELEC-20260001' FROM '([0-9]+)') AS group1, -- 同上
substring('CN-ELEC-20260001' FROM '([A-Z]+)-([A-Z]+)') AS first_group,-- 只返回第一个组
substring('CN-ELEC-20260001' FROM '\d{4}(\d{4})') AS year_part, -- 捕获组精确取值
substring('CN-ELEC-20260001' FROM '^([A-Z]{2})') AS country; -- 锚定开头text
no_group | group1 | first_group | year_part | country
----------+--------+-------------+-----------+---------
20260001 | 20260001 | CN | 0001 | CN要点：substring('...' FROM '([A-Z]+)-([A-Z]+)') 只返回 CN 而非 CN-ELEC，因为存在捕获组时只取第一个组。想要完整匹配，就不要加括号：
sql
SELECT substring('CN-ELEC-20260001' FROM '[A-Z]+-[A-Z]+') AS whole_match;text
whole_match
-------------
CN-ELEC若匹配失败，返回 NULL（不是空串）：
sql
SELECT
substring('CN-ELEC-20260001' FROM '([a-z]+)') AS no_match, -- NULL
substring('abc' FROM '\d+') AS no_digit,
coalesce(substring('abc' FROM '\d+'), '-') AS with_default;text
no_match | no_digit | with_default
----------+----------+--------------
| | -正则变体的性能明显低于位置截取，因为每次都要编译并运行正则引擎。固定位置一律用 FROM s FOR n。
截取与索引：为什么不能直接索引
在 WHERE 中对列做截取会破坏索引可用性：
sql
-- 错误！列上套函数，索引失效
EXPLAIN (COSTS OFF)
SELECT id FROM products WHERE left(sku, 2) = 'CN';text
QUERY PLAN
----------------------------------------
Seq Scan on products
Filter: ("left"(sku, 2) = 'CN'::text)正确做法是改写成前缀匹配，或建立表达式索引：
sql
-- 方案 A：前缀匹配（配合 text_pattern_ops 索引可用）
EXPLAIN (COSTS OFF)
SELECT id FROM products WHERE sku LIKE 'CN-%';
-- 方案 B：表达式索引
CREATE INDEX idx_products_country ON products ((left(sku, 2)));
ANALYZE products;
EXPLAIN (COSTS OFF)
SELECT id FROM products WHERE left(sku, 2) = 'CN';text
QUERY PLAN
--------------------------------------------------------
Index Scan using idx_products_country on products
Index Cond: ("left"(sku, 2) = 'CN'::text)注意：left 是 IMMUTABLE（其结果只依赖入参，不依赖会话设置），因此可以用于表达式索引；而 substring 同样可用。但正则变体因为依赖驱动参数，一般也能用但需谨慎。
验证易变性：
sql
SELECT
p.proname,
CASE p.provolatile
WHEN 'i' THEN 'IMMUTABLE'
WHEN 's' THEN 'STABLE'
WHEN 'v' THEN 'VOLATILE'
END AS volatility
FROM pg_proc p
WHERE p.proname IN ('left', 'right', 'substring', 'substr', 'btrim', 'ltrim', 'rtrim', 'trim')
AND p.pronamespace = 'pg_catalog'::regnamespace
ORDER BY p.proname, volatility;text
proname | volatility
-----------+------------
btrim | IMMUTABLE
left | IMMUTABLE
ltrim | IMMUTABLE
right | IMMUTABLE
rtrim | IMMUTABLE
substring | IMMUTABLE全部是 IMMUTABLE，均可安全用于表达式索引。
实战示例
场景一：清洗脏数据
需求：name 带首尾空格，batch_no 有 NULL、空串、带空格三种脏形态，需要统一成干净的可比较值。
sql
-- 步骤 1：先看清脏数据分布
SELECT
id,
'[' || name || ']' AS raw_name,
case
when batch_no is null then 'NULL'
when batch_no = '' then 'EMPTY'
else '[' || batch_no || ']'
end AS raw_batch
FROM products
ORDER BY id;text
id | raw_name | raw_batch
----+--------------------+--------------------
1 | [ 无线鼠标 ] | [ B20260922-01 ]
2 | [机械键盘] | [B20260922-02]
3 | [PostgreSQL 指南] | NULL
4 | [SQL 进阶] | [ B20260923-07 ]
5 | [咖啡豆] | EMPTYsql
-- 步骤 2：统一清洗，并统计会变化的行数
SELECT
count(*) AS total,
count(*) FILTER (WHERE name <> btrim(name)) AS name_dirty,
count(*) FILTER (WHERE batch_no IS DISTINCT FROM nullif(btrim(batch_no), '')) AS batch_dirty
FROM products;text
total | name_dirty | batch_dirty
-------+------------+-------------
5 | 2 | 3sql
-- 步骤 3：执行清洗（先看更新后的效果，再决定是否落库）
SELECT
id,
nullif(btrim(name), '') AS clean_name,
nullif(btrim(batch_no), '') AS clean_batch
FROM products
ORDER BY id;text
id | clean_name | clean_batch
----+-------------------+----------------
1 | 无线鼠标 | B20260922-01
2 | 机械键盘 | B20260922-02
3 | PostgreSQL 指南 |
4 | SQL 进阶 | B20260923-07
5 | 咖啡豆 |第 5 行的 ' '（原为空串）清洗后得到 NULL，语义统一。
场景二：从 SKU 中解析结构化信息
需求：sku 格式固定为 国家-类别-编号（如 CN-ELEC-20260001），要拆出三段并可分别查询。
sql
-- 步骤 1：先用位置截取解析（格式固定时最快）
SELECT
sku,
substring(sku FROM 1 FOR 2) AS country, -- CN
substring(sku FROM 4 FOR 4) AS category, -- ELEC
substring(sku FROM 9) AS serial, -- 20260001
left(sku, 2) AS c2,
right(sku, 8) AS s8
FROM products
ORDER BY id;text
sku | country | category | serial | c2 | s8
-------------------+---------+----------+----------+----+----------
CN-ELEC-20260001 | CN | ELEC | 20260001 | CN | 20260001
CN-ELEC-20260002 | CN | ELEC | 20260002 | CN | 20260002
CN-BOOK-20260001 | CN | BOOK | 20260001 | CN | 20260001
US-BOOK-20260002 | US | BOOK | 20260002 | US | 20260002
CN-FOOD-20260001 | CN | FOOD | 20260001 | CN | 20260001sql
-- 步骤 2：校验解析结果与原始分段一致（防格式漂移）
SELECT count(*) AS mismatch_rows
FROM products
WHERE sku <> (substring(sku FROM 1 FOR 2) || '-' || substring(sku FROM 4 FOR 4)
|| '-' || substring(sku FROM 9));text
mismatch_rows
---------------
0sql
-- 步骤 3：格式可能变化时，改用正则解析更健壮
SELECT
sku,
substring(sku FROM '^([A-Z]{2})') AS country,
substring(sku FROM '^[A-Z]{2}-([A-Z]+)') AS category,
substring(sku FROM '(\d+)$') AS serial
FROM products
ORDER BY id;text
sku | country | category | serial
-------------------+---------+----------+----------
CN-ELEC-20260001 | CN | ELEC | 20260001
CN-ELEC-20260002 | CN | ELEC | 20260002
CN-BOOK-20260001 | CN | BOOK | 20260001
US-BOOK-20260002 | US | BOOK | 20260002
CN-FOOD-20260001 | CN | FOOD | 20260001sql
-- 步骤 4：按解析结果聚合，验证业务可用
SELECT
substring(sku FROM 1 FOR 2) AS country,
substring(sku FROM 4 FOR 4) AS category,
count(*) AS cnt,
min(price) AS min_price,
max(price) AS max_price
FROM products
GROUP BY 1, 2
ORDER BY 1, 2;text
country | category | cnt | min_price | max_price
---------+----------+-----+-----------+-----------
CN | BOOK | 1 | 89.50 | 89.50
CN | ELEC | 2 | 129.00 | 499.00
CN | FOOD | 1 | 58.00 | 58.00
US | BOOK | 1 | 69.00 | 69.00注意事项
substring的起始位置从 1 开始，0等价于1，不要按「从 0 开始」的直觉写：sql-- 错误！以为取前 3 个 SELECT substring('abcdef' FROM 0 FOR 3); -- 正确 SELECT left('abcdef', 3);count不能为负，会报negative substring length not allowed；想要负数语义请用left/right。trim的参数是字符集合，不是子串：sql-- 错误！以为删掉前缀 'abc'，实际删掉了集合 {a,b,c} 中的所有字符 SELECT btrim('abcabcXYZabc', 'abc'); -- 正确 SELECT regexp_replace('abcabcXYZabc', '^abc', '');trim(' ')返回空串而非NULL，落库前用nullif(trim(x), '')统一空值语义。btrim/ltrim/rtrim与trim的简写等价关系要记清：trim(str)=btrim(str)，trim(LEADING FROM str)=ltrim(str)，trim(TRAILING FROM str)=rtrim(str)。- 正则变体有捕获组时只返回第一个组，想要完整匹配就不要加括号。
- 正则变体匹配失败返回
NULL，不是空串，用于拼接时会被传染，必要时coalesce。 substring/left/right按字符计数，不会切坏多字节字符；但按字节截取需求（如定长协议的原始字节）要用substring(col::bytea, 1, 4)。WHERE中做截取会让索引失效，改写为前缀匹配LIKE 'CN-%'或建立表达式索引。left/right是IMMUTABLE可以建表达式索引，但substring的正则变体表达式索引需实测确认计划是否命中。btrim处理不了字符串中间的空格（如'a b'中的双空格），压缩内部空白要用regexp_replace(x, '\s+', ' ', 'g')。right(str, 0)与left(str, 0)都返回空串，不是NULL，参与length校验时注意区分。
最佳实践
- 格式固定时用位置截取（
substring FROM s FOR n、left/right），性能远优于正则。 - 格式不固定时用正则变体，但务必用
^/$锚定，避免匹配到中间片段。 - 需要多个字段时优先用
regexp_match返回数组，比多次调用substring更清晰高效：sqlSELECT (regexp_match(sku, '^([A-Z]{2})-([A-Z]+)-(\d+)$'))[1] AS country, (regexp_match(sku, '^([A-Z]{2})-([A-Z]+)-(\d+)$'))[2] AS category FROM products; - 清洗流程固定为
nullif(btrim(col), '')，一步同时解决首尾空格与空串到NULL的归一。 - 更新前先
SELECT预览，确认清洗结果符合预期再执行UPDATE。 - 解析结果要做一致性校验（如把分段重新拼回原值比对），防止格式漂移导致静默错误。
WHERE中不要包裹函数，改写为前缀匹配或建表达式索引，可用EXPLAIN验证。substring与substr选一个统一风格，substr是历史别名，新代码建议用标准写法substring(... FROM ... FOR ...)。- 对定长字段用
char而非substring：若字段本身就是定长（如 2 位国家码），建模时就用char(2)，避免运行期计算。 - 处理二进制协议时显式转
bytea，substring(col::bytea FROM 1 FOR 4)才按字节切分。