SQL 数据分析 · 指南 · 2026年10月30日 · 10 分钟阅读

SQL 字符串函数大全：CONCAT、SUBSTRING、REPLACE、UPPER、TRIM 用法与实例

有了字符串函数，你可以直接在查询里清洗和改造文本，不用先导出到表格软件。日常工作用到的就那么几个：|| 和 CONCAT 把文本拼起来，SUBSTRING、LEFT、RIGHT 和 SPLIT_PART 截取其中一段，REPLACE 和 REGEXP_REPLACE 替换字符，UPPER、LOWER 和 TRIM 统一格式让值能对得上，LENGTH 和 POSITION 负责测长度和查找位置。本文用真实数据逐个演示，并指出 PostgreSQL、DuckDB 和其他数据库不一致的地方。

所有查询都在 Nomto SQL 课程的航空公司数据库上运行，使用 PostgreSQL 语法，引擎是 DuckDB（Nomto SQL 练习场背后的引擎）。下面的结果表都是真实输出。会用到的文本列：

字符串拼接：|| 和 CONCAT

在 PostgreSQL 和 DuckDB 里，拼接字符串的标准写法是 || 运算符。CONCAT() 是做同一件事的函数：

SELECT airport_code,
       city,
       airport_name,
       city || ' (' || airport_code || ')' AS label,
       CONCAT(city, ' - ', airport_name) AS full_name
FROM airports
WHERE city IN ('Moscow', 'Sochi')
ORDER BY airport_code;

两者并不完全等价，区别在于对 NULL 的处理：

SELECT 'PG' || NULL AS pipes,
       CONCAT('PG', NULL) AS concat_fn,
       concat_ws(' / ', 'SVO', NULL, 'LED') AS ws;

用 || 时，只要有一个 NULL，整个结果就变成 NULL。CONCAT 会跳过 NULL。concat_ws（concatenate with separator，带分隔符拼接）在各个值之间插入分隔符，同样跳过 NULL，用可选字段拼地址或航线标签时很顺手。如果你用 || 拼标签，结果有些行是空的，通常就是某一列为 NULL；用 COALESCE(col, '') 包一下，或者改用 CONCAT。

其他数据库。 || 和 CONCAT 在 PostgreSQL、DuckDB、Oracle 和 SQLite 里都能用。MySQL 用 CONCAT（在 MySQL 里 || 默认表示逻辑 OR，除非开启特定模式），SQL Server 用 + 或 CONCAT。

UPPER、LOWER 与首字母大写

UPPER 和 LOWER 转换大小写。乘客姓名是全大写存储的：

SELECT passenger_name,
       lower(passenger_name) AS lower_name,
       upper(left(split_part(passenger_name, ' ', 1), 1))
         || lower(substring(split_part(passenger_name, ' ', 1), 2)) AS first_name,
       length(passenger_name) AS len
FROM tickets
LIMIT 3;

PostgreSQL 有 initcap(passenger_name)，一步就能把每个单词首字母大写（Valeriy Tikhonov）。DuckDB 没有 initcap，所以上面的查询手动实现：第一个字母大写，其余小写。

LOWER 更大的用处是匹配。字符串比较区分大小写：WHERE city = 'moscow' 什么也查不到。要么比较 lower(city) = 'moscow'，要么用 ILIKE，也就是不区分大小写的 LIKE：

SELECT COUNT(*) FILTER (WHERE airport_name LIKE '%ovo') AS like_ovo,
       COUNT(*) FILTER (WHERE city ILIKE 'novo%')       AS ilike_novo
FROM airports;

ILIKE 在 PostgreSQL 和 DuckDB 里可用，MySQL 和 SQL Server 不支持；在这两个数据库里，是否区分大小写取决于列的排序规则（collation）。

SUBSTRING、LEFT、RIGHT 和 SPLIT_PART：截取文本

substring(text, start, length) 返回字符串的一部分。位置从 1 开始；省略长度就一直取到末尾。航班号由航空公司代码 PG 加四位数字组成：

SELECT flight_no,
       substring(flight_no, 3) AS num,
       CAST(substring(flight_no, 3) AS INTEGER) AS num_int,
       right(flight_no, 4) AS r
FROM flights
WHERE flight_id IN (1, 2);

num 仍然是文本（'0405'）；如果要按数值排序或比较，需要转换类型，否则 '1000' 会排在 '999' 前面。LEFT(text, n) 和 RIGHT(text, n) 是取前 n 个或后 n 个字符的简写。PostgreSQL 也接受标准写法 substring(text FROM 3 FOR 4)；MySQL 和 SQL Server 同样用 SUBSTRING(text, 3, 4)，Oracle 则叫 SUBSTR。

如果文本里有分隔符，用 split_part(text, delimiter, n) 比数位置简单。把乘客姓名拆成名和姓，再用 POSITION 实现同样的效果：

SELECT passenger_name,
       split_part(passenger_name, ' ', 1) AS first_name,
       split_part(passenger_name, ' ', 2) AS last_name,
       position(' ' IN passenger_name) AS space_at,
       substring(passenger_name FROM 1 FOR position(' ' IN passenger_name) - 1) AS first2,
       left(passenger_name, 1) AS initial
FROM tickets
LIMIT 3;

在 PostgreSQL 和 DuckDB 里，position(' ' IN x) 和 strpos(x, ' ') 是一回事；找不到子串时返回 0。split_part 在 PostgreSQL、DuckDB 和 Snowflake 里都有；MySQL 用 SUBSTRING_INDEX 代替，SQL Server 两者都没有。

截取出来的部分可以直接拿来分组。机票上最常见的名字：

SELECT split_part(passenger_name, ' ', 1) AS first_name,
       COUNT(*) AS tickets
FROM tickets
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;

REPLACE 和 REGEXP_REPLACE

replace(text, from, to) 替换所有出现的子串：

SELECT model,
       replace(model, 'Sukhoi ', '') AS short,
       replace(model, '-', ' ') AS no_dash
FROM aircrafts
WHERE model LIKE 'S%' OR model LIKE 'Bombardier%';

找不到子串时，值原样返回。替换成 '' 就等于删除。

如果要替换的是一种模式而不是固定文本，就用正则表达式。座位号由排号和字母组成（10A），去掉数字或字母就能把它拆开：

SELECT seat_no,
       regexp_replace(seat_no, '[0-9]', '', 'g') AS letter,
       CAST(regexp_replace(seat_no, '[A-Z]', '', 'g') AS INTEGER) AS row_no
FROM seats
WHERE aircraft_code = '319'
ORDER BY row_no, letter
LIMIT 4;

'g' 标志表示「全局」：替换所有匹配，而不只是第一个。不加的话，regexp_replace('12A', '[0-9]', '') 得到的是 2A。这一点 PostgreSQL 和 DuckDB 一样。如果要把匹配内容提取出来而不是替换，PostgreSQL 用 substring(text FROM 'pattern') 或 regexp_substr（15 版及以上），DuckDB 用 regexp_extract(text, 'pattern')。

TRIM、LTRIM、RTRIM 和 LPAD

trim 去掉首尾空格。两个看起来一样的值在 JOIN 里对不上，最常见的原因就是多了空格：

SELECT '|' || trim('  SVO  ')  || '|' AS t,
       '|' || ltrim('  SVO  ') || '|' AS l,
       '|' || rtrim('  SVO  ') || '|' AS r,
       trim(BOTH '0' FROM '0405') AS z,
       ltrim('0405', '0') AS lz;

竖线是为了让空格看得见。trim 也能去掉其他字符，比如开头的 0。length(' SVO ') 是 6，而 length(trim(' SVO ')) 是 3，这是快速找出脏数据的办法：WHERE length(code) <> length(trim(code))。

lpad(text, length, fill) 反过来，在左边补齐。求 PG0405 的下一个航班号，并保持四位数字：

SELECT flight_no,
       lpad(CAST(CAST(substring(flight_no, 3) AS INTEGER) + 1 AS VARCHAR), 4, '0') AS next_no
FROM flights
WHERE flight_id = 1;

JSON 里的文本：->>

联系方式存在一个 JSON 列里。->> 运算符把某个字段作为文本返回，之后所有字符串函数都能用：

SELECT ticket_no,
       contact_data ->> 'phone' AS phone,
       contact_data ->> 'email' AS email,
       right(contact_data ->> 'phone', 4) AS last4
FROM tickets
LIMIT 3;

不存在的键返回 NULL，而不是空字符串。366,733 张机票中有 199,083 张填了邮箱，可以用 COUNT(*) FILTER (WHERE contact_data ->> 'email' IS NOT NULL) 统计。->> 在 PostgreSQL 里适用于 json 和 jsonb，在 DuckDB 里适用于 JSON。

STRING_AGG：把多行合并成一个字符串

和拆分相反：把一组的值合并成一个用逗号分隔的单元格。拥有不止一个机场的城市：

SELECT city,
       string_agg(airport_code, ', ' ORDER BY airport_code) AS airports,
       COUNT(*) AS n
FROM airports
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY city;

string_agg 在 PostgreSQL、DuckDB 和 SQL Server（2017 及以上）里可用。MySQL 叫 GROUP_CONCAT，Oracle 和 Snowflake 叫 LISTAGG。HAVING 的用法见 GROUP BY 和 HAVING 的区别。

速查：PostgreSQL、DuckDB 与其他数据库对照

常见错误

• 对可能为 NULL 的列用 ||。 一个 NULL 就让整个标签变空。改用 CONCAT、concat_ws 或 COALESCE。
• 比较前没有统一格式。 末尾带空格的 'SVO ' 和小写的 'svo' 永远不等于 'SVO'。用文本键做 JOIN 之前，两边都要 trim，并统一 lower 或 upper。
• 对以文本存储的数字排序。 先把截取出来的数字转成 INTEGER，再 ORDER BY 或求 MAX。
• 位置差一。 SQL 字符串从位置 1 开始，不是 0；而且 position 找不到时返回 0，所以对没有分隔符的值，substring(x, 1, position(...) - 1) 会返回空字符串。
• regexp_replace 忘了加 'g' 标志，结果只改了第一个匹配。
• 用双引号包文本。 在 PostgreSQL 里 "SVO" 是列名，文本要用单引号。

日期有自己的一套函数，见 SQL 日期函数。想用真实的面试风格题目多练习，可以看 SQL 练习题。

常见问题

SQL 里怎么拼接字符串？

在 PostgreSQL 和 DuckDB 里用 first || ' ' || last 或 CONCAT(first, ' ', last)。CONCAT 会忽略 NULL，|| 只要有一部分是 NULL 就返回 NULL。MySQL 用 CONCAT；SQL Server 用 + 或 CONCAT。

SUBSTRING 和 SPLIT_PART 有什么区别？

SUBSTRING 按位置截取（第 3 到第 6 个字符）。SPLIT_PART 按分隔符截取（按空格拆开后的第二个词）。各段长度不固定、但用已知字符分隔时，用 SPLIT_PART。

SQL 怎么去掉字符串里的空格？

trim(x) 去掉两端的空格，ltrim 和 rtrim 只去一边。要去掉所有空格，包括中间的，用 replace(x, ' ', '')。

SQL 比较怎么不区分大小写？

比较转成小写后的值，lower(city) = 'moscow'，或者在 PostgreSQL 和 DuckDB 里用 ILIKE。在 MySQL 和 SQL Server 里，比较往往本来就不区分大小写，取决于排序规则。

PostgreSQL 有 LEN 函数吗？

没有。用 length(x) 或 char_length(x)。LEN 是 SQL Server 的函数；注意 SQL Server 的 LEN 会忽略末尾空格，而 PostgreSQL 对 text 用 length 时会把末尾空格算进去。

其他语言： English, Español, Português (Brasil), Tiếng Việt