40 字符串与数值函数

转换字符串，并使用常见的数值、舍入和随机数函数。

PostgreSQL 内置了数百个函数。字符串函数和数学函数涵盖了日常使用中的大多数需求：大小写转换、填充、去除空白、拆分、替换、哈希计算；舍入、绝对值、取模、乘方和随机数。熟悉这些常用函数后，你会经常用到它们。

本章要点

常见字符串函数：upper、lower、length、substring、position、trim、replace、split_part
使用 || 和 concat 进行字符串拼接
使用 ~、~*、regexp_replace、regexp_match 处理正则表达式
常见数值函数：abs、round、ceil、floor、mod、power、random
用于发现更多函数的 \df 元命令

示例

示例 40.1 字符串与数值函数

-- Case and length
SELECT upper('hello') AS u, lower('HELLO') AS l, length('hello') AS len;

-- Substring: SQL syntax and function syntax
SELECT substring('postgresql' FROM 1 FOR 8) AS sql_form,
       substring('postgresql', 1, 8)        AS fn_form;

-- Position: 1-based index, 0 if not found
SELECT position('gre' IN 'postgresql') AS pos;

-- Trim and replace
SELECT trim('  hi  ')                         AS trimmed,
       trim(both '/' FROM '/path/to/file/')   AS path_trim,
       replace('a-b-c', '-', '.')             AS replaced;

-- Splitting
SELECT split_part('a.b.c.d', '.', 2)                  AS second_part,
       string_to_array('a,b,c', ',')                  AS as_array;

-- Concatenation: || vs concat. concat tolerates NULL; || does not.
SELECT 'a' || NULL AS pipe_null,
       concat('a', NULL, 'b') AS concat_null;

-- Regex: ~ matches, ~* is case-insensitive
SELECT 'PostgreSQL' ~ 'gres' AS match,
       'PostgreSQL' ~* 'POSTGRE' AS imatch,
       regexp_replace('a1b2c3', '[0-9]', '#', 'g') AS no_digits;

-- Numeric: abs, round, ceil, floor, mod, power, random
SELECT abs(-5)                AS a,
       round(3.14159, 2)      AS r,
       ceil(2.1)              AS c,
       floor(2.9)             AS f,
       mod(10, 3)             AS m,
       power(2, 10)           AS p,
       round((random() * 100)::numeric, 2) AS rand;

substring 有两种形式：SQL 标准的 substring(str FROM start FOR n) 和函数调用形式 substring(str, start, n)。两者的下标都从 1 开始。

concat 和 || 对 NULL 的处理不同：'a' || NULL 的结果是 NULL（任一操作数都会使整个表达式变为 NULL），而 concat('a', NULL, 'b') 返回 'ab'——concat 会把 NULL 当作空字符串处理。请明确选择所需的行为。

正则表达式操作符 ~ 和 ~* 是用于匹配的简写。regexp_replace(text, pattern, replacement [, flags]) 是执行替换的主要函数；'g' 标志表示“替换所有匹配项”（默认只替换第一个）。regexp_match 返回捕获组。

数值函数大多顾名思义。random() 返回 [0, 1) 范围内的 double precision；如需生成从 0 到 n − 1 的整数，请使用 floor(random() * n)::integer。直接进行类型转换会舍入，而不是截断。若需要密码学安全的随机数，请使用 pgcrypto 提供的 gen_random_bytes。

运行示例

运行方式：

$ psql -f source/string-and-numeric-functions.sql postgres
   u   |   l   | len
-------+-------+-----
 HELLO | hello |   5
(1 row)
 ...

常见陷阱

不要把 substring 的下标与以 0 为起点的编程语言混淆——PostgreSQL 的下标从 1 开始。
replace 会按字面匹配字符串进行替换，不是正则表达式。处理正则表达式时，请使用 regexp_replace。
random() 不具有密码学安全性。对于涉及安全的场景，请使用 pgcrypto 的 gen_random_bytes。
length 统计字符数；octet_length 统计字节数。对于多字节文本（UTF-8），两者的结果不同。
round(numeric) 遇到舍入中点时向远离零的方向舍入：round(0.5) 的结果是 1，round(-0.5) 的结果是 -1。round(double precision) 通常将舍入中点舍入到偶数，但具体行为取决于平台。

提示

在交互式 psql 中，\df *upper* 会列出名称中包含“upper”的所有函数。\df+ upper 会显示源代码和所有者。这是发现可用函数的绝佳方式。

动手练习

生成一个标签：SELECT upper(name) || ' [' || length(name) || ']' FROM fruits;。将所有平均值舍入：SELECT customer_id, round(avg(total), 2) FROM orders_example GROUP BY customer_id;。使用正则表达式查找以元音开头的名称：SELECT name FROM fruits WHERE name ~* '^[aeiou]';。

40 字符串与数值函数

本章要点

示例

运行示例

常见陷阱

下载