sql-string-functions

SQL 字符串函数

SQL 提供了一套丰富的内置函数来操作字符串数据。这些函数对于清洗、格式化和分析基于文本的信息至关重要。虽然函数名称在不同的 SQL 方言（例如 PostgreSQL、MySQL、SQL Server）之间可能略有不同，但核心概念是通用的。我们将重点介绍最常用、标准的函数。

字符串连接：组合字符串

字符串连接是将两个或多个字符串连接在一起的过程。

• CONCAT(string1, string2, ...): 连接字符串。如果任何参数为 NULL，在某些数据库（如 SQL Server）中结果为 NULL，而在其他数据库（如 MySQL/PostgreSQL）中则会忽略 NULL。
• CONCAT_WS(separator, string1, string2, ...): 带分隔符的连接。这非常有用，因为它会在元素之间放置一个分隔符，并智能地跳过 NULL 值。

SELECT CONCAT('John', ' ', 'Doe') AS full_name;
-- 结果: 'John Doe'

SELECT CONCAT_WS(', ', 'New York', 'NY', NULL, 'USA') AS address;
-- 结果: 'New York, NY, USA'

改变大小写：UPPER 和 LOWER

这些函数用于将字符串转换为全大写或全小写，这对于不区分大小写的比较特别有用。

SELECT UPPER('Hello World') AS upper_case, LOWER('Hello World') AS lower_case;
-- 结果: 'HELLO WORLD', 'hello world'

-- 不区分大小写的搜索
SELECT * FROM users WHERE LOWER(username) = 'admin';

长度和修剪：LENGTH、TRIM、LTRIM、RTRIM

这些函数帮助您管理空白字符并查找字符串的长度。

• LENGTH(string) 或 LEN(string): 返回字符串中的字符数。（名称因数据库而异）。
• TRIM(string): 移除字符串开头和结尾的空白字符。
• LTRIM(string): 移除字符串开头的（左侧）空白字符。
• RTRIM(string): 移除字符串结尾的（右侧）空白字符。

SELECT LENGTH('  sql is fun  ') AS with_spaces;
-- 结果: 14

SELECT LENGTH(TRIM('  sql is fun  ')) AS trimmed;
-- 结果: 11

子字符串：SUBSTRING、LEFT、RIGHT

这些函数允许您提取字符串的一部分。

• SUBSTRING(string FROM start FOR length): 提取子字符串的标准语法。许多数据库也支持 SUBSTRING(string, start, length)。
• LEFT(string, number_of_chars): 从字符串左侧提取指定数量的字符。
• RIGHT(string, number_of_chars): 从字符串右侧提取指定数量的字符。

SELECT SUBSTRING('abcdef' FROM 3 FOR 2) AS part;
-- 结果: 'cd'

SELECT LEFT('report-2023.pdf', 6) AS type, RIGHT('report-2023.pdf', 3) AS extension;
-- 结果: 'report', 'pdf'

搜索和替换：POSITION、REPLACE

这些函数用于查找或更改字符串的一部分。

• POSITION(substring IN string): 查找子字符串的起始位置。如果未找到则返回 0。（其他名称：CHARINDEX、INSTR）。
• REPLACE(string, from_substring, to_substring): 将字符串中所有出现的子字符串替换为另一个。

SELECT POSITION('world' IN 'hello world') AS found_at;
-- 结果: 7

SELECT REPLACE('Your total is $100', '$', 'USD') AS updated_price;
-- 结果: 'Your total is USD 100'

重要注意事项

性能

在 WHERE 子句中对列使用函数可能会阻止数据库在该列上使用索引，从而可能导致全表扫描和非常慢的查询。例如，WHERE LOWER(username) = 'admin' 通常是不可索引的。一些现代数据库支持“基于函数的索引”（function-based indexes）来解决此问题，但这需要明确设置。

安全性（SQL 注入）

关键： 绝不能使用字符串函数来构造包含用户提供数据的 SQL 查询。这是导致 SQL 注入漏洞的直接途径。务必始终使用参数化查询（预处理语句）来安全地将用户数据插入到查询中。

-- 危险 - 不要这样做！
-- 字符串拼接来构建查询。
query = "SELECT * FROM users WHERE username = '" + userInput + "';";

-- 安全 - 使用此方法
-- 参数化查询（语法因语言/库而异）
query = "SELECT * FROM users WHERE username = ?;";
execute(query, [userInput]);