课程 11.1 · 阅读时间：~10分钟

本课程专注于SQL中的实用字符串处理。您将学习如何清理文本值、标准化大小写、提取有用片段，并为分析和报告构建可读字段。我们将通过使用Sakila数据库的实际场景进行讲解。课程结束时，您将能够自信地直接在SQL中准备文本数据以进行分析。

SQL中的实用字符串处理

在上一个模块中，我们讨论了SQL代码质量和查询性能。现在我们转向应用分析：真实数据集通常包含文本字段，这些字段不仅需要被选择，还需要首先转换为可用的形式。

在报告、用户细分、参考数据清理、导出准备和数据质量检查中，需要进行实用的字符串处理。这正是分析师和开发人员在日常工作中面临的任务。

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

基本字符串函数本身是有用的，但当您将它们应用于具体任务时，真正的价值才会显现。例如，相同的电子邮件值可以用于数据质量检查、基于域的细分和市场报告。

在实践中，SQL字符串处理通常分为四种任务类型：

• 清理文本中的多余空格和重复模式；
• 标准化大小写和格式；
• 提取字符串的部分以进行分析；
• 为接口和报告构建新的文本字段。

字符串处理的基本工作流程

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

1. 清理值；
2. 将其标准化为一致的格式；
3. 提取所需部分；
4. 在分析或报告中使用结果。

这种方法使查询更可预测，更易于调试。

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中很方便，但其他数据库管理系统可能需要不同的语法。
• 不要试图在一行中解决所有数据清理逻辑；分阶段处理文本。

本课的关键要点：

• 实用的SQL字符串处理对于清理、标准化、提取和验证文本数据是必要的。
• TRIM、LOWER、REPLACE、SUBSTRING_INDEX、LEFT、RIGHT和CONCAT_WS在日常工作中尤其有用。
• 在分析之前，文本应标准化为一致的格式，以避免错误的分组和比较。
• SQL不仅可以格式化字符串，还可以快速揭示数据质量问题。
• 最大的价值来自于将函数结合在一个清晰的逐步工作流程中。

常见问题

如果表值看起来已经准备好，为什么还要标准化文本？

因为即使在视觉上干净的数据中也可能包含多余的空格、不一致的大小写或微妙的格式偏差。标准化使过滤、分组和比较更可靠。

为什么提取电子邮件域进行分析是有用的？

域有助于细分用户、识别公司地址和检测数据异常。这是将原始文本转换为分析特征的简单方法。

在应用程序中构建文本字段而不是在SQL中构建文本字段何时更好？

当字段需要用于报告、管理接口、导出或中间分析时。在这些情况下，SQL侧的标签构建减少了后处理，并将逻辑保持在数据附近。

面试问题

您在分析SQL中使用字符串函数解决哪些类型的任务？

典型任务包括文本清理、格式标准化、特征提取和数据验证。在实践中，字符串函数通常在分组、细分和报告字段生成之前使用。

为什么在文本列上对GROUP BY之前应用TRIM()和LOWER()是有用的？

如果没有标准化，相同的值可能由于不同的大小写或多余的空格而出现在多个组中。预清理提高了聚合的正确性，减少了虚假的差异。

您如何用实际示例解释SUBSTRING_INDEX()的价值？

在MySQL中，这个函数方便用于通过分隔符快速提取字符串部分。例如，您可以提取电子邮件域，并立即将其用于用户细分或分析报告。

在下一课中，我们将转向SQL进行数据分析和报告，并看看如何将准备好的数据转化为有用的商业洞察。