九、SQL 常见函数之文本数据处理

上一篇我们介绍了 SQL 中常见的数值函数。接下来我们继续学习用于处理文本数据的字符函数。

字符函数

字符函数用于字符数据的处理，例如字符串的拼接、大小写转换、子串的查找和替换等。下表列出了 SQL 中常见的字符函数：

我们通过示例说明一下这些函数的作用。

字符与 ASCII 编码转换

ASCII 函数用于返回字符串中第一个字符的 ASCII 编码，CHR 和 CHAR 函数用于将 ASCII 码转换为对应的字符：

-- Oracle 和 PostgreSQL 实现
SELECT ASCII('SQL'), CHR(83)   FROM employee  WHERE emp_id = 1;
ASCII('SQL')|CHAR(83)|
------------|--------| 
          83|S       |
-- MySQL 和 SQL Server 实现
SELECT ASCII('SQL'), CHAR(83)   FROM employee  WHERE emp_id = 1;
ASCII('SQL')|CHAR(83)|
------------|--------|
          83|S       |

大写字母 S 的 ASCII 编码为 83。Oracle 和 PostgreSQL 实现了 CHR 函数，MySQL 和 SQL Server 实现了 CHAR 函数。

字符串连接

连接操作用于将两个或者多个字符串拼接到一起，CONCAT 函数是执行连接操作的标准函数：

SELECT CONCAT('SQL', ' World')   FROM employee  WHERE emp_id = 1;
CONCAT('SQL', ' World')|
-----------------------|
SQL World              |

Oracle 中的 CONCAT 函数一次只能连接两个字符串；其他数据库可以一次连接多个字符串：CONCAT(str1, str2, …)。
CONCAT 函数还有一个扩展形式：CONCAT_WS，可以指定连接的分隔符：

-- MySQL、SQL Server 以及 PostgreSQL 实现
SELECT CONCAT_WS('-','S', 'Q', 'L')   FROM employee  WHERE emp_id = 1;
CONCAT_WS('-','S', 'Q', 'L')|
----------------------------|
S-Q-L                       |

复制
该语句使用“-”将多个字符串连接到一起。Oracle 不支持 CONCAT_WS 函数。
除了 CONCAT 函数之外，还可以使用连接符将字符串进行连接：

-- Oracle 和 PostgreSQL 实现
SELECT 'S' || '-' || 'Q' || '-' || 'L'   FROM employee  WHERE emp_id = 1;
'S'||'-'||'Q'||'-'||'L'|
-----------------------|
S-Q-L                  |
-- SQL Server 实现 
SELECT 'S' + '-' + 'Q' + '-' + 'L'   FROM employee  WHERE emp_id = 1;
| -----|
  S-Q-L|

复制
Oracle 和 PostgreSQL 使用 || 连接字符串，SQL Server 使用 + 连接字符串。

大小写转换

字符串的大小写转换也是数据处理中常见的操作。LOWER 函数将字符转换为小写，UPPER 函数将字符转换为大写：

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 函数。

字符串长度

字符串的长度可以按照两种方式进行计算：字符数量和字节数量。在多字节编码中，一个字符可能占用多个字节。CHAR_LENGTH 函数用于计算字符串包含的字符数量，OCTET_LENGTH 用于计算字符串包含的字节数量：

-- MySQL 和 PostgreSQL 实现
SELECT CHAR_LENGTH('数据库'), OCTET_LENGTH('数据库')   FROM employee  WHERE emp_id = 1;
CHAR_LENGTH('数据库')|OCTET_LENGTH('数据库')|
  ------------------|-------------------|
                  3|                  9|

字符串“数据库”包含 3 个字符，在 UTF-8 编码中占用了 9 个字节。MySQL 和 PostgreSQL 实现了这两个标准的函数。
Oracle 中使用 LENGTH 函数和 LENGTHB 函数计算字符数量和字节数量：

-- Oracle 实现
SELECT LENGTH('数据库'), LENGTHB('数据库')   FROM employee  WHERE emp_id = 1; 
LENGTH('数据库')|LENGTHB('数据库')|
-------------|--------------| 
3|             9|

PostgreSQL 中也可以使用 LENGTH 函数计算字符数量。 MySQL 中的 LENGTH 函数计算的是字节数量。
SQL Server 中使用 LEN 函数和 DATALENGTH 函数计算字符数量和字节数量：

-- SQL Server 实现
SELECT LEN('数据库') AS LEN,        DATALENGTH('数据库') AS DATALENGTH   FROM employee  WHERE emp_id = 1; 
LEN|DATALENGTH|
---|----------|
  3|         6|

字符串“数据库”在“Chinese_PRC_CI_AI_WS”字符集中占用 6 个字节，因为每个汉字占用 2 个字节。

获取子串

SUBSTRING 函数或者 SUBSTR 函数用于返回字符串中的子串：

-- MySQL、SQL Server 以及 PostgreSQL 实现
SELECT emp_name, SUBSTRING(emp_name, 1, 1)   FROM employee  WHERE emp_id = 1;
-- Oracle、MySQL 以及 PostgreSQL 实现
SELECT emp_name, SUBSTR(emp_name, 1, 1)   FROM employee  WHERE emp_id = 1;

复制
函数中的第二个参数表示子串的起始位置，第三个参数表示子串的长度。以上查询返回了员工姓名中的第一个字符，结果如下：

emp_name|SUBSTRING(emp_name, 1, 1)|
--------|-------------------------|
刘备     |刘                       |

复制
在 Oracle 和 MySQL 中，起始位置可以是负数，表示从右往左计算起始位置。

截断字符串

TRIM 函数用于删除字符串开头和结尾的指定字符：

SELECT TRIM('-' FROM '--S-Q-L--'), TRIM('  S-Q-L  ')   FROM employee  WHERE emp_id = 1;
TRIM('-' FROM '--S-Q-L--')|TRIM('  S-Q-L  ')|
--------------------------|-----------------|
S-Q-L                     |S-Q-L            |

复制
第一个 TRIM 函数删除了两端的“-”；第二个 TRIM 删除了两端的空白字符。
另外，LTRIM 函数可以用于删除字符串左侧的字符，RTRIM 函数可以用于删除字符串右侧的字符。

查找与替换

INSTR 函数用于在字符串中查找并返回子串的位置，没有找到时返回 0。REPLACE 函数用于替换字符串中的子串。以下是各种数据库中的具体实现：

-- Oracle 和 MySQL 实现
SELECT email, INSTR(email, '@'), REPLACE(email, '@', '.')   FROM employee  WHERE emp_id = 1;
-- SQL Server 实现
SELECT email, PATINDEX('%@%', email), REPLACE(email, '@', '.')   FROM employee  WHERE emp_id = 1;
-- PostgreSQL 实现
SELECT email, POSITION('@' IN email), REPLACE(email, '@', '.')   FROM employee  WHERE emp_id = 1;

复制
以上查询语句的结果如下：

email            |INSTR(email, '@')|REPLACE(email, '@', '.')|
-----------------|-----------------|------------------------|
liubei@shuguo.com|                7|liubei.shuguo.com       |

“@”是“liubei@shuguo.com”中的第 7 个字符，INSTR 函数返回了数字 7。SQL Server 使用 PATINDEX 函数查找子串，PostgreSQL 使用 POSITION 函数查找子串。REPLACE 函数将电子邮箱中的“@”替换为“.”。

小结

本篇介绍了常见的字符函数，掌握这些函数可以方便我们对文本数据进行清洗和转换等处理。除了这些函数之外，各种数据库还提供了大量的字符处理函数。当我们需要实现某种操作时，可以先查找数据库的文档，避免重复实现已有的功能。
思考题：为了保护员工的隐私，在显示信息时将员工姓名进行隐藏处理：对于两个字的姓名，将姓氏显示为星号；对于三个字或更多字的姓名，将倒数第二个字显示为星号。如何使用 SQL 语句实现下面的效果？