Menu

PostgreSQL 字符串函数:substring、concat、split_part

日常最常用的 PostgreSQL 字符串函数:substring、left 和 right、|| 和 concat、split_part、position、replace、regexp_replace、trim、lpad 和 format。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

最常用的 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 之外没有限制。所有字符串函数两者都能接受。

Coddy 编程语言插图

和 Coddy 一起学编程

开始