PostgreSQL - 字符串函数

PostgreSQL：现代字符串函数指南

处理文本是数据库管理中的常见任务。PostgreSQL 提供了一套全面的函数，用于处理和转换字符串数据。为了便于学习，我们不只是简单地列出它们，而是根据其用途进行分组。

在我们的示例中，假设我们有一个 users 表：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    full_name VARCHAR(100)
);

INSERT INTO users (username, email, full_name) VALUES
('jdoe', 'john.doe@example.com', '  John Doe  '),
('asmith', 'anna.smith@web.dev', 'Anna Smith'),
('pg_user', 'user@postgresql.org', 'PostgreSQL User');

1. 拼接：组合字符串

组合字符串是最常见的操作之一。

函数/运算符	描述	示例
||	标准的 SQL 拼接运算符。因其简洁性而受到推荐。	SELECT ‘Hello’ || ’ ’ || ‘World’; — 返回 ‘Hello World’
CONCAT(str1, str2, …)	一个拼接多个参数的函数。它方便地忽略 NULL 参数。	SELECT CONCAT(full_name, ’ <’, email, ’>’) FROM users WHERE id = 2; — ‘Anna Smith anna.smith@web.dev’
CONCAT_WS(sep, str1, …)	使用分隔符进行拼接。第一个参数是用于放置在其他参数之间的分隔符。	SELECT CONCAT_WS(’ ’, ‘PostgreSQL’, ‘is’, ‘awesome’); — ‘PostgreSQL is awesome’

2. 更改大小写和修剪空格

清理用户输入通常涉及标准化大小写和删除多余的空格。

函数	描述	示例
LOWER(str)	将字符串转换为全小写。	SELECT LOWER(‘PostgreSQL’); — ‘postgresql’
UPPER(str)	将字符串转换为全大写。	SELECT UPPER(‘PostgreSQL’); — ‘POSTGRESQL’
INITCAP(str)	将每个单词的首字母转换为大写，其余转换为小写。	SELECT INITCAP(‘hello world’); — ‘Hello World’
TRIM([BOTH|LEADING|TRAILING] [chars] FROM str)	移除开头和/或结尾的字符（默认为空格）。	SELECT TRIM(full_name) FROM users WHERE id = 1; — ‘John Doe’

3. 子字符串和分割

提取字符串的特定部分对于解析数据至关重要。

函数	描述	示例
SUBSTRING(str FROM pos FOR len)	提取子字符串。FOR len 部分是可选的。	SELECT SUBSTRING(‘PostgreSQL’ FROM 1 FOR 4); — ‘Post’
LEFT(str, n) / RIGHT(str, n)	获取字符串前或后 n 个字符的便捷函数。	SELECT LEFT(email, 4) FROM users WHERE id = 1; — ‘john’
SPLIT_PART(str, delim, field)	按分隔符分割字符串，并返回第 N 部分（1-indexed）。	SELECT SPLIT_PART(email, ’@’, 2) FROM users WHERE id = 1; — ‘example.com’
POSITION(substr IN str)	查找子字符串的起始位置。	SELECT POSITION(’@’ IN email) FROM users WHERE id = 1; — 9

4. 替换和获取长度

函数	描述	示例
REPLACE(str, from, to)	将字符串中所有出现的子字符串替换为另一个。	SELECT REPLACE(‘one two one’, ‘one’, ‘three’); — ‘three two three’
LENGTH(str)	返回字符串中的字符数。	SELECT LENGTH(username) FROM users WHERE id = 3; — 7
CHAR_LENGTH(str)	LENGTH() 的同义词。	SELECT CHAR_LENGTH(username) FROM users WHERE id = 3; — 7

5. 高级：正则表达式和格式化

对于复杂的模式匹配和格式化，PostgreSQL 提供了强大的工具。

• 正则表达式：使用 ~ 运算符进行匹配（email ~ '.+\.com$'），REGEXP_REPLACE 用于高级替换，REGEXP_MATCHES 用于提取匹配组。
• FORMAT(format_str, …)：一个用于构建格式化字符串的强大函数，类似于其他语言中的 printf。它比复杂的拼接更安全，通常也更清晰。示例：SELECT FORMAT('User %s has ID %s.', full_name, id) FROM users WHERE id = 2;

小型项目：生成用户资料数据

让我们组合一些函数来生成一个简洁的资料摘要：

SELECT
    TRIM(full_name) AS cleaned_name,
    LOWER(username) AS username_lower,
    SPLIT_PART(email, '@', 1) AS email_user,
    SPLIT_PART(email, '@', 2) AS email_domain
FROM users
WHERE id = 1;

此查询演示了在单个实际语句中清理数据（TRIM）、标准化（LOWER）和解析（SPLIT_PART）的功能。