最常用的 PostgreSQL 字符串函数:
substring(text FROM start FOR length)截取字符串的一部分。位置从 1 开始。||或concat()拼接字符串。split_part(text, delimiter, n)取出分隔字符串中的某一段。replace(text, from, to)替换子串的每一次出现。
Ada 得到 Ada Lovelace 和域名 example.com。concat_ws 跳过了 Lin 缺失的姓,所以她的全名是 Lin,末尾没有多余的空格。
截取字符串的一部分
结果依次是 PostgreS、gre、Post、SQL、Postgre 和 0042。
substring(... FROM start FOR length)是 SQL 标准写法;substr(text, start, length)是作用相同的普通函数。省略长度就截取到字符串末尾。left或right中的负数表示除了最后(或最前)n 个字符之外的全部。- 如果
FROM后面是正则表达式,substring返回第一个匹配;如果模式中有括号分组,就返回第一个分组。
拼接字符串:||、concat 和 concat_ws
只要有一边是 NULL,|| 就返回 NULL。concat 把 NULL 当作空字符串,返回 ab。concat_ws 只在非 NULL 的值之间放分隔符,返回 Ada, London。当 || 一边是文本时,另一边的数字会被转换,比如 id-42。
要把多行的值拼接成一个字符串,用 string_agg。
分割、查找和替换
结果依次是 example.com、c、4、2026/11/17、a#b#c# 和 {red,green,blue}。
- 指定的段不存在时,
split_part返回空字符串。负数的段号从末尾开始数(PostgreSQL 14 起)。 position(sub IN text)和strpos(text, sub)返回从 1 开始的位置,找不到时返回 0。replace替换每一次出现,并且区分大小写。regexp_replace只替换第一个匹配,除非传入'g'标志;加上'i'可以忽略大小写。regexp_split_to_table(text, pattern)把字符串分割成多行,而不是数组。
在 WHERE 中做模式判断(LIKE、ILIKE、~),见 LIKE、ILIKE 和正则表达式。
清理、填充和格式化
结果依次是 hello、hi、00042、Hello World、5 和 Ada has 3 items。ltrim 和 rtrim 只去掉一侧。如果字符串已经比目标长度更长,lpad 和 rpad 会把它截断。
length 统计字符数,octet_length 统计字节数;对于 é 这样的非 ASCII 文本,两者不同。format 用 %s 表示值,%I 表示加引号的标识符,%L 表示加引号的字面量。因此在函数中拼接动态 SQL 时,用它比较安全。
速查表
| 任务 | 函数 | 示例结果 |
|---|---|---|
| 转大写、转小写 | upper(s), lower(s) | ADA, ada |
| 以某个前缀开头 | starts_with(s, 'pre') 或 s LIKE 'pre%' | true |
| 重复、反转 | repeat('ab', 3), reverse('abc') | ababab, cba |
| 正则的第一个匹配 | substring(s FROM 'pattern') | 匹配到的文本 |
| 正则的所有匹配 | regexp_matches(s, 'pattern', 'g') | 每个匹配一行 |
| 按格式把数字转成文本 | to_char(1234.5, 'FM9,999.00') | 1,234.50 |
其余函数见官方字符串函数参考。
常见问题
PostgreSQL 中怎么判断一个字符串是否包含某个子串?
用 position('needle' IN haystack) > 0 或 strpos(haystack, 'needle') > 0,在 WHERE 子句中也可以用 haystack LIKE '%needle%'。用 ILIKE 可以忽略大小写,判断前缀用 starts_with(haystack, 'prefix')。
为什么 PostgreSQL 中 || 会返回 NULL?
只要 || 的任意一个操作数是 NULL,结果就是 NULL。可以改用会跳过 NULL 参数的 concat() 或 concat_ws(),或者用 COALESCE(value, '') 把可能为空的值包起来。
PostgreSQL 字符串的位置从 1 开始吗?
是的。位置从 1 开始,所以 substring('abc' FROM 1 FOR 1) 是 a,找不到文本时 position 返回 0。
PostgreSQL 中 text 和 varchar 有什么区别?
在速度和存储上没有区别。varchar(n) 多了一个长度检查;text 和不带长度的 varchar 除了单个值最大 1 GB 之外没有限制。所有字符串函数两者都能接受。