41. 数学与字符串函数

本节目标：熟悉常用的数学运算、四舍五入函数，以及字符串拼接、截取、大小写、去空格等处理手法。

直接用前面已经建好的 orders 表来试，先确认里面有数据：

postgres=# SELECT id, amount FROM orders LIMIT 5;

数学函数

加减乘除直接用运算符。几个常用函数：

postgres=# SELECT
postgres=#   abs(-7.5),          -- 绝对值 7.5
postgres=#   round(3.456, 2),    -- 四舍五入到2位 3.46
postgres=#   trunc(3.456, 2),    -- 截断到2位 3.45
postgres=#   ceil(3.1),          -- 向上取整 4
postgres=#   floor(3.9),         -- 向下取整 3
postgres=#   power(2, 10),       -- 2的10次方 1024
postgres=#   sqrt(16),           -- 平方根 4
postgres=#   mod(10, 3);         -- 取余 1

再来几个容易被忽略的：

postgres=# SELECT
postgres=#   sign(-5),           -- 符号：-1 / 0 / 1
postgres=#   div(10, 3),         -- 整数除 3
postgres=#   cbrt(27),           -- 立方根 3
postgres=#   gcd(12, 18),        -- 最大公约数 6
postgres=#   lcm(4, 6);          -- 最小公倍数 12

Note

round 是四舍五入， trunc 是直接砍掉小数位。想要”舍”还是”入”，别用错。金额、评分这类通常要 round，统计原始值可能要 trunc。

随机数用 random()，返回 0 到 1 之间的小数。想让它可复现，先用 setseed() 设种子：

postgres=# SELECT random();
postgres=# SELECT setseed(0.5);
postgres=# SELECT random();  -- 同一会话里后面几次结果固定

width_bucket 可以把数值分桶，做直方图统计很方便：

postgres=# SELECT width_bucket(amount, 0, 1000, 4) AS bucket, COUNT(*)
postgres=# FROM orders GROUP BY bucket ORDER BY bucket;

字符串拼接

拼接用 || 操作符，或 concat() 函数。

postgres=# SELECT 'Hello' || ' ' || 'World';          -- Hello World
postgres=# SELECT concat('订单号:', id) FROM orders;   -- 自动忽略 NULL

concat_ws 用指定分隔符拼接，第一个参数是分隔符：

postgres=# SELECT concat_ws('-', '2026', '07', '31');  -- 2026-07-31

Warning

|| 和 concat 对 NULL 的处理不同。'a' || NULL 结果是 NULL；而 concat('a', NULL) 会忽略 NULL，结果是 'a'。别混用导致拼接结果莫名其妙变成空。

大小写与去空格

postgres=# SELECT lower('PostgreSQL');  -- postgresql
postgres=# SELECT upper('sql');         -- SQL
postgres=# SELECT initcap('hello world'); -- Hello World
postgres=# SELECT trim('  hi  ');       -- hi（去两端空格）
postgres=# SELECT ltrim('xxhi', 'x');   -- hi（去左）
postgres=# SELECT rtrim('hixx', 'x');   -- hi（去右）

Tip

去空格最常用 trim()。如果只想去某一侧，用 ltrim / rtrim 并指定要去掉的字符集，比如 trim 默认去空白， ltrim('00abc','0') 去前导 0。

截取与查找

substring 按位置截取，从 1 开始数：

postgres=# SELECT substring('PostgreSQL' from 2 for 4);  -- ostg

left / right 取左 / 右 N 个：

postgres=# SELECT left('abcdef', 3);   -- abc
postgres=# SELECT right('abcdef', 2);  -- ef

length 算字符数， position 找子串位置（找不到返回 0）。strpos 是同样功能、参数顺序相反：

postgres=# SELECT length('中文abc');               -- 5
postgres=# SELECT position('sql' in 'postgresql');  -- 8
postgres=# SELECT strpos('postgresql', 'sql');      -- 8

replace 替换， split_part 按分隔符取第 N 段：

postgres=# SELECT replace('a-b-c', '-', '/');          -- a/b/c
postgres=# SELECT split_part('a@b@c', '@', 2);         -- b

还有几个实用的：

postgres=# SELECT overlay('Txxxxas' placing 'Postgre' from 2 for 4);  -- TPostgreas
postgres=# SELECT repeat('ab', 3);            -- ababab
postgres=# SELECT reverse('abc');             -- cba
postgres=# SELECT lpad('7', 3, '0');          -- 007
postgres=# SELECT translate('a1b2', '12', 'xy');  -- axby

正则处理

regexp_replace 按正则替换， regexp_match 提取匹配到的分组：

postgres=# SELECT regexp_replace('abc123', '\d+', '#');   -- abc#
postgres=# SELECT regexp_match('订单号A123', '(\d+)');     -- {123}

Note

regexp_match 返回的是数组（一个分组就一个元素）。要取某个分组用 regexp_match(...)[1]。正则写不对，结果是空数组，排查时多看一眼。

格式化输出

format 类似 C 语言的 printf，用 %s 占位：

postgres=# SELECT format('用户 %s 余额 %s', 'alice', 100);
-- 用户 alice 余额 100

Tip

想安全拼动态 SQL，用 format('%I', col) 自动给标识符加引号，用 format('%L', val) 给字面量加引号，避免 SQL 注入。比如 format('SELECT * FROM %I', tabname)。

数值格式化 to_char

数字也能用 to_char 排版，比如固定小数位、加千分位：

postgres=# SELECT to_char(12345.6, '99,999.99');  -- 12,345.60
postgres=# SELECT to_char(0.123, '0.000');        -- 0.123

9 表示可选位，0 表示强制位（不足补 0），, 是千分位。金额展示常用。

按组分组合并：string_agg

string_agg 是聚合函数，把一组行的文本拼成一个用分隔符连接的串。比如把每个用户的订单金额拼成 100,200,300：

postgres=# SELECT u.username,
postgres=#        string_agg(o.amount::text, ',' ORDER BY o.id) AS amounts
postgres=# FROM users u
postgres=# JOIN orders o ON o.user_id = u.id
postgres=# GROUP BY u.username;

Note

string_agg 和 array_agg 类似，区别是前者返回字符串，后者返回数组。要进一步当数组用就选 array_agg。

类型转换 CAST 与 ::

函数参数类型不对时，常需要转换。PostgreSQL 用 CAST(值 AS 类型) 或简写 值::类型：

postgres=# SELECT CAST('123' AS INT);   -- 123
postgres=# SELECT '123'::INT;           -- 123
postgres=# SELECT '2026-07-31'::DATE;   -- 日期

Warning

转换可能失败抛错。比如 'abc'::INT 会直接报错。不确定内容干净时，先判断再转，或用 NULLIF 配合处理异常值。

示例：拼一个用户简介串

把几个字段拼成一句人话，记得用 COALESCE 兜住 NULL：

postgres=# SELECT username || ' 年龄 ' || COALESCE(age::text, '未知')
postgres=# FROM users;

Tip

拼字符串时别忘了 COALESCE 处理 NULL，否则一个 NULL 让整段拼接变空。concat 会自动忽略 NULL，追求稳妥就用它。

这些函数到底解决什么问题

光记函数名没用，关键是什么场景该用哪个。算金额时 round(价格, 2) 比 trunc 更符合”四舍五入”的财务习惯；拼接用户标签用 concat_ws 比 || 省心，因为它自动跳过 NULL；要把一组值拼成报表用 string_agg。我刚学的时候走过最长的弯路，就是拿一堆 || 硬拼，结果某列是 NULL 时整段变空，调试半天才反应过来该用 concat。

字符集与编码的坑

PostgreSQL 默认用 UTF-8，一个汉字算一个字符，length('中文') 返回 2，而不是字节数。涉及中文截取（比如 left(姓名, 1) 取姓）在 UTF-8 下完全正常，不用额外处理。但如果你存的是 bytea 或老编码，长度函数行为会不同，这点要心里有数。

一个综合小例子

假设你想给每个用户生成一句简介：“alice，28 岁，邮箱 alice@x.com”。用字符串函数一行就能拼：

postgres=# SELECT username || '，' || age || ' 岁，邮箱 ' || email FROM users;

Tip

例子里 age 是 INT，和字符串拼接时 PostgreSQL 会替你做隐式转换。想完全可控，显式写 age::text 更稳，也避免将来改类型时出错。

字符串函数还有哪些值得记

除了上面这些，日常还常用 translate（按字符映射替换，比 replace 更细）、overlay（在指定位置覆盖子串）、reverse（反转）。它们出现频率不如 substring 高，但碰到特殊需求时能省不少事。建议先把这些函数名混个脸熟，真用的时候再翻文档细看参数。

另外提醒一句：函数名大小写不敏感，但建议统一小写书写，和 SQL 关键字风格保持一致，读起来更舒服。

Tip

生成「整齐编号」离不开补零。想给数字补成固定宽度，比如把 123 变成 000123，用 lpad(数字::text, 6, '0')。反过来要去前导零，用 ltrim('00123', '0')。订单号、流水号、批次号这类展示型字段，几乎天天要用这两招。我之前为了对齐表格，硬是用 || 拼了一堆 '0'，后来才知道 lpad 一行搞定，又稳又好看。数字和文本混排时，先 ::text 再 lpad 最稳。

常见误区

把 || 当 concat 用，结果遇到 NULL 整段变 NULL。
substring 位置从 1 开始数，写成 0 会取不到。
length 算的是”字符数”不是”字节数”。中文在 UTF-8 下占 3 字节，但 length('中文') 仍是 2。
用 trim 想去指定字符却没传第二参数，结果只去掉了空白。

数学与字符串函数

41. 数学与字符串函数

数学函数

字符串拼接

大小写与去空格

截取与查找

正则处理

格式化输出

数值格式化 to_char

按组分组合并：string_agg

类型转换 CAST 与 ::

更多字符串小工具

示例：拼一个用户简介串

这些函数到底解决什么问题

字符集与编码的坑

一个综合小例子

字符串函数还有哪些值得记

常见误区