# 字符串与数值函数

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

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="40.1" caption="字符串与数值函数"}
-- 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`。

## 运行示例 {#run}

运行方式：

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

## 常见陷阱 {#pitfalls}

- 不要把 `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)` 通常将舍入中点舍入到偶数，但具体行为取决于平台。

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

> [!NOTE] 动手练习
> 生成一个标签：`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]';`。

## 下载 {#downloads}

SQL 文件：[string-and-numeric-functions.sql](/examples/source/string-and-numeric-functions.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)