SQL 字符函数用于字符数据的处理，例如字符串的拼接、大小写转换、子串的查找和替换等。
本文比较五种主流数据库常用字符函数的实现和差异，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。
|
字符函数
|
函数功能
|
MySQL
|
Oracle
|
SQL Server
|
PostgreSQL
|
SQLite
|
|
CHAR_LENGTH(s)
|
返回字符串s包含的字符数量
|
✔️
|
LENGTH(s)
|
LEN(s)
|
✔️
|
LENGTH(s)
|
|
CONCAT(s1, s2, …)
|
连接字符串
|
✔️
|
✔️
|
✔️
|
✔️
|
||
|
|
INSTR(s, s1)
|
返回子串首次出现的位置
|
✔️
|
✔️
|
PATINDEX(s1, s)
|
POSITION(s1 IN s)
|
✔️
|
|
LOWER(s)
|
返回字符串s的小写形式
|
✔️
|
✔️
|
✔️
|
✔️
|
✔️
|
|
OCTET_LENGTH(s)
|
返回字符串s包含的字节数量
|
✔️
|
LENGTHB(s)
|
DATALENGTH(s)
|
✔️
|
❌
|
|
REPLACE(s, old, new)
|
将字符串中的old替换为new
|
✔️
|
✔️
|
✔️
|
✔️
|
✔️
|
|
SUBSTRING(s, n, m)
|
返回从位置n开始的m个字符
|
✔️
|
SUBSTR(s, n, m)
|
✔️
|
✔️
|
✔️
|
|
TRIM(s1 FROM s)
|
删除字符串开头和结尾的子串
|
✔️
|
✔️
|
✔️
|
✔️
|
TRIM(s, s1)
|
|
UPPER(s)
|
返回字符串s的大写形式
|
✔️
|
✔️
|
✔️
|
✔️
|
✔️
|
下面我们通过一些示例来说明这些函数的作用和注意事项。
字符串的长度
字符串的长度可以按照两种方式进行计算：字符数量和字节数量。在多字节编码中，一个字符可能占用多个字节。
CHAR_LENGTH(s) 函数用于计算字符串中的字符数量，OCTET_LENGTH(s) 函数用于计算字符串包含的字节数量。例如：
-- MySQL 和 PostgreSQL
SELECT CHAR_LENGTH('数据库'), OCTET_LENGTH('数据库');
查询返回的结果如下：
CHAR_LENGTH('数据库')|OCTET_LENGTH('数据库')
--------------------|---------------------
3| 9
字符串“数据库”包含 3 个字符，在 UTF-8 编码中占用 9 个字节。MySQL 和 PostgreSQL 实现了这两个标准函数。
Oracle 使用 LENGTH(s) 函数和 LENGTHB 函数计算字符数量和字节数量，例如：
-- Oracle
SELECT LENGTH('数据库'), LENGTHB('数据库')
FROM dual;
查询返回的结果和上面的示例相同。
提示：PostgreSQL 也提供了 LENGTH(s) 函数，用于返回字符串中的字符数量。MySQL 也提供了 LENGTH(s) 函数，用于返回字符串中的字节数量。
Microsoft SQL Server 使用 LEN(s) 函数和 DATALENGTH(s) 函数计算字符数量和字节数量，例如：
-- SQL Server
SELECT LEN('数据库'), DATALENGTH('数据库');
查询返回的结果如下：
LEN|DATALENGTH
---|----------
3| 6
字符串“数据库”在“Chinese_PRC_CI_AS”字符集中占用 6 个字节，每个汉字占用 2 个字节。
SQLite 只提供了 LENGTH(s) 函数，用于计算字符串中的字符个数，例如：
-- SQLite
SELECT LENGTH('数据库');
查询返回的结果如下：
LENGTH('数据库')
--------------
3
连接字符串
CONCAT(s1, s2, …) 函数将两个或者多个字符串连接到一起，组成一个新的字符串。例如：
-- MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT CONCAT('S', 'Q', 'L');
查询返回的结果如下：
CONCAT('S', 'Q', 'L')
---------------------
SQL
Oracle 中的 CONCAT 函数一次只能连接两个字符串，例如：
SELECT CONCAT(CONCAT('S', 'Q'), 'L')
FROM dual;
我们通过嵌套函数调用连接多个字符串，查询返回的结果和上面的示例相同。
SQLite 没有提供连接字符串的函数，可以通过连接运算符（||）实现字符串的连接。例如：
-- SQLite、Oracle 以及 PostgreSQL
SELECT 'S' || 'Q' || 'L';
查询返回的结果和上面的示例相同。
提示：Oracle 和 PostgreSQL 也提供了连接运算符（||），Microsoft SQL Server 使用加号（+）作为连接运算符。
除 CONCAT 函数外，还有一个 CONCAT_WS(separator, s1, s2 , … ) 函数，可以使用指定分隔符连接字符串。例如：
-- MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT CONCAT_WS('-','S', 'Q', 'L');
查询返回的结果如下。
CONCAT_WS('-','S', 'Q', 'L')
----------------------------
S-Q-L
MySQL、Microsoft SQL Server 以及 PostgreSQL 实现了该函数。
大小写转换
LOWER(s) 函数将字符串转换为小写形式，UPPER(s) 函数将字符串转换为大写形式。例如：
SELECT LOWER('SQL'), UPPER('sql')
FROM employee
WHERE emp_id = 1;
查询返回的结果如下：
LOWER('SQL')|UPPER('sql')
------------|------------
sql |SQL
提示：MySQL 中的 LCASE 函数等价于 LOWER 函数，UCASE 函数等价于 UPPER 函数。Oracle 和 PostgreSQL 还提供了首字母大写的 INITCAP 函数。
获取子串
SUBSTRING(s, n, m) 函数返回字符串 s 中从位置 n 开始的 m 个字符子串。例如：
-- MySQL、Microsoft SQL Server、PostgreSQL 以及 SQlite
SELECT SUBSTRING('数据库', 1, 2);
查询返回的结果如下：
SUBSTRING('数据库', 1, 2)
-----------------------
数据
Oracle 使用简写的 SUBSTR(s, n, m) 函数返回子串，例如：
-- Oracle、MySQL、PostgreSQL 以及 SQLite
SELECT SUBSTR('数据库', 1, 2)
FROM dual;
MySQL、PostgreSQL 以及 SQLite 也支持 SUBSTR 函数。查询结果和上面的示例相同。
另外，Oracle、MySQL 以及 SQLite 中的起始位置 n 可以指定负数，表示从字符串的尾部倒数查找起始位置，然后再返回子串。例如：
-- Oracle、MySQL 以及 SQLite
SELECT SUBSTR('数据库', -2, 2)
FROM employee
WHERE emp_id = 1;
查询返回的结果如下。
SUBSTR('数据库', -2, 2)
---------------------
据库
其中，-2 表示从右往左数第 2 个字符（“据”），然后再返回 2 个字符。
提示：MySQL、Microsoft SQL Server 以及 PostgreSQL 提供了 LEFT(s, n) 和 RIGHT(s, n) 函数，分别用于返回字符串开头和结尾的 n 个字符。
子串查找与替换
INSTR(s, s1) 函数查找并返回字符串 s 中子串 s1 第一次出现的位置。如果没有找到子串，则会返回 0。例如：
-- Oracle、MySQL 以及 SQLite
SELECT email, INSTR(email, '@')
FROM employee
WHERE emp_id = 1;
查询返回的结果如下：
email |INSTR(email, '@')
-----------------|-----------------
liubei@shuguo.com| 7
“@”是字符串“[email protected]”中的第 7 个字符。
Microsoft SQL Server 使用 PATINDEX(s1, s) 函数查找子串的位置，例如：
-- Microsoft SQL Server
SELECT email, PATINDEX('%@%', email)
FROM employee
WHERE emp_id = 1;
其中，s1 参数的形式为 %pattern%，类似于 LIKE 运算符中的匹配模式。查询返回的结果和上面的示例相同。
PostgreSQL 使用 POSITION (s1 IN s) 函数查找子串的位置，例如：
-- PostgreSQL
SELECT email, POSITION('@' IN email)
FROM employee
WHERE emp_id = 1;
查询返回的结果和上面的示例相同。
REPLACE(s, old, new) 函数将字符串 s 中的子串 old 替换为 new。例如：
SELECT email, REPLACE(email, 'com', 'net')
FROM employee
WHERE emp_id = 1;
查询返回的结果如下：
email |REPLACE(email, 'com', 'net')
-----------------|----------------------------
liubei@shuguo.com|liubei@shuguo.net
REPLACE 函数在 5 种主流数据库中的实现一致。
截断字符串
TRIM(s1 FROM s) 函数删除字符串 s 开头和结尾的子串 s1。例如：
-- Oracle、MySQL、Microsoft SQL Server 以及 PostgreSQL
SELECT TRIM('-' FROM '--S-Q-L--'), TRIM(' S-Q-L ')
FROM employee
WHERE emp_id = 1;
第一个函数删除了开头和结尾的“-”；第二个函数省略了 s1 子串，默认表示删除开头和
结尾的空格。查询返回的结果如下：
TRIM('-' FROM '--S-Q-L--')|TRIM(' S-Q-L ')
--------------------------|-----------------
S-Q-L |S-Q-L
Oracle 中的参数 s1 只能是单个字符，其他数据库中的参数 s1 可以是多个字符。
SQLite 中的 TRIM(s, s1) 函数的调用格式与其他数据库不同，例如：
-- SQLite
SELECT TRIM('--S-Q-L--', '-'), TRIM(' S-Q-L ');
查询返回的结果和上面的示例相同。
提示：LTRIM(s)函数可以删除字符串开头的空格，RTRIM(s)函数可以删除字符串尾部的空格，这两个函数是 TRIM 函数的简化版。
