SQL 字符串处理实战

第 11.1 课 · 阅读时间：约 10 分钟 · 练习题 ↓

本课聚焦 SQL 中的实用字符串处理。你将学习如何清理文本值、统一大小写、提取有用的片段，以及为分析和报表构建易读的字段。我们会使用 Sakila 数据库演示实际场景。学完本课后，你将能够直接在 SQL 中熟练地为分析准备文本数据。

在上一个模块中，我们讨论了 SQL 代码质量和查询性能。现在转向实际分析：真实数据集中常常包含文本字段，这些字段不能只是查询出来，而要先转换成可用的形式。

报表、用户分群、参考数据清理、导出准备以及数据质量检查，都需要实用的字符串处理。这些正是分析师和开发人员日常工作中会遇到的任务。

SQL 字符串处理实战：清理文本、提取邮箱域名以及构建分析字段

为什么实用的字符串处理很重要

基本字符串函数本身就很有用，但只有把它们应用到具体任务中，才能体现真正的价值。例如，同一个邮箱值既可以用于数据质量检查，也可以用于按域名分群和营销报表。

在实践中，SQL 字符串处理通常可以归为四类任务：

清除文本中多余的空格和重复的片段；
统一大小写和格式；
提取字符串的一部分用于分析；
为界面和报表构建新的文本字段。

字符串处理的基本流程

大多数情况下，文本是分步处理的：

清理值；
把它规范化为统一的格式；
提取需要的部分；
在分析或报表中使用结果。

这种做法让查询更可预期，也更容易调试。

SELECT
   LOWER(TRIM(email)) AS email_normalized
FROM customer
LIMIT 5;

结果：邮箱去掉了首尾空格并转换为小写。

清理和规范化文本

最常见的场景是为后续分析准备字符串。为此通常使用 TRIM()、LOWER()、UPPER() 和 REPLACE()。

示例：规范化邮箱

SELECT
   customer_id,
   email,
   LOWER(TRIM(email)) AS email_normalized
FROM customer
LIMIT 10;

说明：即使数据看起来已经很干净，规范化也能改善比较、分组以及后续的自动化处理。

示例：清理地址

SELECT
   address_id,
   address,
   TRIM(REPLACE(address, 'Street', 'St.')) AS address_cleaned
FROM address
LIMIT 10;

结果：地址变得更短、更统一，便于用于报表和界面。

提取字符串中有用的部分

清理之后，分析时往往只需要字符串的某一部分。在 MySQL 中，SUBSTRING()、LEFT()、RIGHT() 和 SUBSTRING_INDEX() 尤其有用。

示例：提取邮箱域名

SELECT
   customer_id,
   email,
   SUBSTRING_INDEX(LOWER(TRIM(email)), '@', -1) AS email_domain
FROM customer
LIMIT 10;

结果：从邮箱中提取出域名部分，例如 example.com。

示例：提取片名的开头

SELECT
   film_id,
   title,
   LEFT(title, 5) AS title_prefix,
   RIGHT(title, 5) AS title_suffix
FROM film
LIMIT 10;

说明：这些片段可用于快速的启发式判断、检查命名规律或生成简短的标签。

构建分析用的文本字段

在分析中，往往需要易读的标签，而不是原始的源字段。为此，CONCAT() 和 CONCAT_WS() 很方便。

示例：用于报表的客户标签

SELECT
   customer_id,
   CONCAT_WS(
      ' | ',
      CONCAT_WS(' ', first_name, last_name),
      LOWER(TRIM(email)),
      CONCAT('store=', store_id)
   ) AS customer_label
FROM customer
LIMIT 10;

结果：得到一个紧凑的文本字段，适用于管理报表、导出文件和内部工具。

校验文本数据质量

字符串处理不仅能用于格式化，还能用于基本的校验。SQL 不能取代完整的校验系统，但能帮你快速发现可疑的值。

示例：查找不含 `@` 的邮箱

SELECT
   customer_id,
   email
FROM customer
WHERE INSTR(LOWER(TRIM(email)), '@') = 0;

结果：查询返回邮箱中不包含必需分隔符的记录。

示例：检查片名长度

SELECT
   film_id,
   title,
   CHAR_LENGTH(title) AS title_length
FROM film
WHERE CHAR_LENGTH(title) > 20
ORDER BY title_length DESC
LIMIT 10;

说明：这类检查适合查找对卡片、界面屏幕或导出限制来说过长的值。

实践示例：按邮箱域名对客户分群

现在把几种技巧组合到一条分析查询中。假设我们想知道客户中哪些域名最常见。

SELECT
   SUBSTRING_INDEX(LOWER(TRIM(email)), '@', -1) AS email_domain,
   COUNT(*) AS customer_count
FROM customer
WHERE email IS NOT NULL
  AND INSTR(LOWER(TRIM(email)), '@') > 0
GROUP BY SUBSTRING_INDEX(LOWER(TRIM(email)), '@', -1)
ORDER BY customer_count DESC, email_domain
LIMIT 15;

结果：得到客户按邮箱域名的分布。这可用于初步了解受众、发现异常以及对沟通渠道分群。

这个例子说明了一个要点：字符串函数串联使用时威力最大。先清理值，再校验结构，然后提取域名，最后才进行聚合。

实用建议

在比较和分组之前先规范化文本。
如果同一个函数在一条查询中多次出现，可以考虑使用 CTE 或子查询来提高可读性。
SUBSTRING_INDEX() 在 MySQL 中很方便，但其他 DBMS 可能需要不同的语法。
不要试图用一行代码完成所有数据清理逻辑，应分阶段处理文本。

本课要点：

实用的 SQL 字符串处理用于清理、规范化、提取和校验文本数据。
TRIM、LOWER、REPLACE、SUBSTRING_INDEX、LEFT、RIGHT 和 CONCAT_WS 在日常工作中特别有用。
分析之前，应把文本规范化为统一格式，以免分组和比较出错。
SQL 不仅能格式化字符串，还能快速发现数据质量问题。
把函数组合成清晰、分步的流程时，价值最大。

常见问题

如果表中的值看起来已经可以用了，为什么还要规范化文本？

因为即使看起来干净的数据，也可能包含多余的空格、不一致的大小写或细微的格式偏差。规范化能让过滤、分组和比较更可靠。

为什么提取邮箱域名对分析有用？

域名有助于对用户分群、识别企业邮箱以及发现数据异常。这是把原始文本转换为分析特征的一种简单方法。

什么时候在 SQL 中而不是在应用中构建文本字段更好？

当这些字段用于报表、管理界面、导出或中间分析时。在这些情况下，在 SQL 端构建标签可以减少后期处理，并让逻辑贴近数据。

面试问题

在分析型 SQL 中，你用字符串函数解决哪些任务？

典型任务包括文本清理、格式规范化、特征提取和数据校验。在实践中，字符串函数常常在分组、分群和生成报表字段之前使用。

为什么在对文本列执行 `GROUP BY` 之前应用 `TRIM()` 和 `LOWER()` 很有用？

如果不规范化，同一个值可能因为大小写不同或多余的空格而出现在多个组中。预先清理能提高聚合的正确性，减少虚假的差异。

你会如何用一个实际例子说明 `SUBSTRING_INDEX()` 的价值？

在 MySQL 中，这个函数便于按分隔符快速提取字符串的一部分。例如，可以提取邮箱的域名，并立即用于用户分群或分析报表。