五种主流数据库：常用字符函数

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, '@')
-----------------|-----------------
[e-mail]| 7

“@”是字符串“[e-mail]”中的第 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')
-----------------|----------------------------
[e-mail]|[e-mail] 

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 函数的简化版。