按文本进行 SQL 筛选

我们将涵盖的内容

• 使用 =（或使用 != 排除）按精确文本匹配筛选行 ›
• 使用 UPPER()、LOWER() 和 TRIM() 忽略大小写和空格 ›
• 使用 IN 和 NOT IN 包含或排除行 ›
• 使用 LIKE 和通配符查找部分匹配项 ›
• 按位置匹配 ›
• 使用 AND 和 OR 按多列进行过滤 ›
• 使用 IS NULL 和 IS NOT NULL 处理缺失值 ›
• 使用正则表达式进行高级文本过滤 ›
• 处理空字符串与 NULL 值 ›
• 使用变量参数化您的过滤器 ›

您可以使用 SQL 的 WHERE 关键字来过滤行。本指南涵盖了按文本列（具有字符串或 varchar 数据类型的列）过滤表的常用方法。

在深入学习高级 SQL 之前需要快速复习吗？请查看我们的SQL 速查表，了解核心命令和语法。它也非常适合与开始进行数据分析的同事分享。

按精确匹配筛选行的 SQL

要搜索精确、区分大小写的匹配项，请使用带有 = 运算符的 WHERE 子句。术语应包含在单引号中。

SELECT
  *
FROM
  products
WHERE
  title = 'Lightweight Wool Computer' -- note single quotes

如果您想排除除标题为“Lightweight wool computer”之外的所有产品，请使用 !=

SELECT
  *
FROM
  products
WHERE
  -- Get all the rows that don't match this term
  title != 'Lightweight Wool Computer'

使用 UPPER() 和 LOWER() 处理文本大小写的 SQL

如果您想忽略大小写，可以将比较的两边都转换为相同的大小写。以下是查找所有标题大小写不敏感匹配的所有产品的 P 方法：

SELECT
  *
FROM
  products
WHERE
  LOWER(title) = LOWER('LIGHTWEIGHT WOOL COMPUTER')

使用 TRIM() 忽略空格的 SQL

如果您的字符串可能包含额外的空格，您可以使用 TRIM() 忽略文本列中的前导或尾随空格。

SELECT
  *
FROM
  products
WHERE
  -- Remove leading and trailing white space characters
  TRIM(title) = 'Lightweight Wool Computer';

使用 IN 按多个搜索词筛选行的 SQL

要搜索多个术语，您可以 = 使用和 OR(s)，如下所示：

SELECT
  *
FROM
  products
WHERE
  -- The equal operator is case sensitive
  title = 'Lightweight Wool Computer'
  OR title = 'Intelligent Paper Hat'

但最好使用 IN（它更易读），如下所示：

SELECT
  *
FROM
  products
WHERE
-- List of exact terms to match, enclosed by parentheses
  title IN (
    'Lightweight Wool Computer', -- Terms separated by commas
    'Intelligent Paper Hat' -- No comma at the end of the list
  );

使用 NOT IN 排除包含精确匹配项的行的 SQL

您还可以使用 NOT IN 来获取所有不是搜索词的行。

SELECT
  *
FROM
  products
WHERE
  -- List of exact terms you want to exclude, enclosed by parentheses
  title NOT IN (
    'Lightweight Wool Computer', -- Terms separated by commas
    'Intelligent Paper Hat'
  );

使用 LIKE 筛选包含部分文本的行的 SQL

要按列是否包含文本/字符串的一部分来筛选行，请使用 LIKE 关键字。例如，如果我们想搜索所有“Lightweight”产品（区分大小写）：

| Title                      | Category |
|----------------------------|----------|
| Lightweight Paper Bottle   | Gadget   |
| Lightweight Wool Bag       | Gadget   |
| Lightweight Leather Gloves | Gadget   |

您可以像这样使用 LIKE：

SELECT
  title,
  category
FROM
  products
WHERE
  -- Term is case sensitive
  title LIKE 'Lightweight%'

LIKE 通常支持两个通配符字符：

• % 是一个通配符运算符，用于匹配零个或多个字符。
• _ 匹配单个字符。

如果您的搜索词包含字面意义上的 % 或 _ 字符，您可以使用 ESCAPE 来告诉数据库引擎将其视为常规字符。此代码筛选标题类似于 Wool % 的内容。

SELECT
  *
FROM
  products
WHERE
  title LIKE 'Wool \%' ESCAPE '\';

“\”是经典的转义字符。但如果“\”在您的数据中有特殊含义，您可以为 ESCAPE 函数提供不同的字符，例如 ESCAPE !。

您还可以将大小写转换技巧与 LIKE 结合使用以进行部分匹配。

SELECT
  *
FROM
  products
WHERE
  -- Use UPPER (or LOWER) to make sure casing doesn't matter
  UPPER(title) LIKE 'LIGHTWEIGHT%';

前导通配符运算符会触发全表扫描。这意味着数据库引擎将不得不检查每一行；它将无法使用列上设置的任何索引。因此，请小心使用前导通配符运算符（例如 %wool）。请参阅 SQL 查询最佳实践。

根据位置筛选字符串部分的 SQL

要根据字符串在某个位置的部分进行过滤，请使用 SUBSTRING()。例如，查找标题以“Small”开头的 P。

| Title                   | Category   |
|-------------------------|------------|
| Small Marble Shoes      | Doohickey  |
| Small Marble Hat        | Doohickey  |
| Small Plastic Computer  | Doohickey  |
| ...                     | ...        |

您可以使用 SUBSTRING。

SELECT
  title,
  category
FROM
  products
WHERE
  -- Filter for the first five letters in the string
  -- FROM determines the starting point (index starts at 1)
  -- FOR determines the number of characters from the starting point
  -- Case sensitive
  SUBSTRING(title FROM 1 FOR 5) = 'Small';

按多列筛选行的 SQL

您可以使用 AND 和 OR 在单个 WHERE 子句中按多列进行过滤。

要获取结果必须同时是“Lightweight”且在 Gizmo 类别中的结果，

| Title                        | Category |
|------------------------------|----------|
| Lightweight Linen Coat       | Gizmo    |
| Lightweight Linen Bottle     | Gizmo    |
| Lightweight Steel Knife      | Gizmo    |
| Lightweight Leather Bench    | Gizmo    |

您将使用带有 AND 的 WHERE 子句。

SELECT
  title,
  category
FROM
  products
WHERE
-- AND will return rows that satisfy both criteria
  title LIKE 'Lightweight%'
  AND category = 'Gizmo';

如果您想要任一产品为 lightweight，或在 Gizmo 类别中的行，像这样：

| Title                     | Category |
|---------------------------|----------|
| Rustic Paper Wallet       | Gizmo    |
| Mediocre Wooden Table     | Gizmo    |
| Sleek Paper Toucan        | Gizmo    |
| Synergistic Steel Chair   | Gizmo    |
| Lightweight Paper Bottle  | Gadget   |
| ...                       | ...      |

使用 OR。

SELECT
  title,
  category
FROM
  products
WHERE
-- OR will return rows that satisfy either criteria
  title LIKE 'Lightweight%'
  OR category = 'Gizmo';

使用括号，您可以进行复杂的条件查询。以下是如何搜索 Gizmo 或 Widget 类别中的轻量级产品：

SELECT
  title,
  category
FROM
  products
WHERE
  title LIKE 'Lightweight%'
  -- Scope the OR conditional with parentheses
  AND (
    category = 'Gizmo'
    OR category = 'Widget'
  )
;

这里的括号是必需的，否则您将返回 Gizmo 类别中有羊毛产品的行，以及所有 Widget 产品，而不管是否有羊毛。

筛选包含或排除缺失值的行（SQL）

有时您想查找文本列为空（null）的行。您可以使用 IS NULL 来做到这一点。

SELECT
  *
FROM
  products
WHERE
  title IS NULL;

该查询将为您提供标题缺失的所有产品。如果您想要相反的结果——即标题存在的行——请使用 IS NOT NULL。

SELECT
  *
FROM
  products
WHERE
  title IS NOT NULL;

使用正则表达式过滤文本的 SQL

为了进行精确搜索，某些数据库允许您使用正则表达式来搜索数据。

假设（出于某种原因）我们想搜索产品标题的最后一个单词恰好是五个字母的所有产品，如下所示：

| Title                        | Category   |
|------------------------------|------------|
| Small Marble Shoes           | Doohickey  |
| Synergistic Granite Chair    | Doohickey  |
| Enormous Aluminum Shirt      | Doohickey  |
| Enormous Steel Watch         | Doohickey  |
| Mediocre Wooden Table        | Gizmo      |
| ...                          | ...        |

我们可以使用正则表达式来过滤行。

SELECT
  title,
  category
FROM
  products
WHERE
  -- You may need to use a different regex function name;
  -- check which function name your database supports
  REGEXP_LIKE (title, '\b[a-zA-Z]{5}\W*$', 'i');

“\b[a-zA-Z]{5}\W*$”是“最后一个单词有 5 个字符”的别名。祝您学习正则表达式好运；大多数人只是根据需要查找特定正则表达式的方法。生成式模型在正则表达式方面越来越好。仅供参考，以下是这些符号的含义：

• \b：单词边界，因此您匹配整个单词，而不是另一个单词的一部分。
• [a-zA-Z]：匹配任何大写或小写字母（ASCII）。
• {5}：“正好五个”这些字母。
• \W*：匹配零个或多个非单词字符（字母、数字或下划线以外的任何字符），以防末尾有标点符号会影响我们的计数。
• $：字符串末尾的位置。

函数调用中第三个位置的 i 代表 insensitive，表示不区分大小写。数据库在是否可以使用正则表达式以及提供哪些函数方面存在差异，因此您需要查找您的数据库是如何执行此操作的。以下是一些示例：

• PostgreSQL：~, ~*
• MySQL：REGEXP, RLIKE, 或 REGEXP_LIKE()

通常，只有在无法使用上述方法筛选行时才使用正则表达式。正则表达式仅对心灵感应实体可读，并且数据库无法利用索引来加快查询时间。

处理空字符串与 NULL 值的 SQL

空字符串（''）和 NULL 值在 SQL 中是不同的，但人们经常将它们混淆。

• 空字符串是一个实际的值，包含零个字符。
• NULL 代表任何值的缺失（因此，甚至不是一个空白字符串）。

查找文本列存在但为空（空白字符串 ''）的行：

SELECT
  title
FROM
  products
WHERE
  title = '';

查找列为 NULL 或为空的行：

SELECT
  title
FROM
  products
WHERE
  title IS NULL OR description = '';

当您试图查找真正“缺失”的数据与故意留空的字段时，这种区别很重要。某些数据库系统将空字符串视为 NULL，但最好明确您正在寻找什么。

SQL 变量过滤器

在 Metabase 中（这仅在 Metabase 中有效），您可以参数化 SQL 查询，以便用户可以在 WHERE 子句中插入值。在这里，我们创建一个变量 title（用双大括号括起来），并使用 CONCAT 将 % 通配符运算符附加到变量上。

SELECT
  title,
  category,
  vendor
FROM
  products
WHERE
  -- You need CONCAT to build the string with the interpolated value
  -- This variable syntax only works in Metabase
  title LIKE CONCAT ({{title}}, '%');

这样，用户在仪表板的筛选器小部件中输入 Lightweight，查询将仅返回 lightweight 产品。

查看 SQL 参数

延伸阅读

• SQL 查询
• 按日期进行 SQL 筛选
• SQL 最佳实践