定价
登录 开始使用
文档 学习
主页
课程
Metabase 基础
提升您的数据技能
速查表

SQL 文本筛选

学习 SQL 文本筛选：使用 WHERE、LIKE、IN、NOT IN、TRIM、UPPER、LOWER、regex 和变量来查找和筛选表中的字符串数据。

使用 Metabase 学习 SQL

免费下载 Metabase，或免费试用 Metabase Cloud

我们将涵盖的内容

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

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

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'

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

如果您想忽略大小写，可以将比较的两端转换为相同的大小写。以下是无论大小写如何，查找所有标题匹配的产品的方法：

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

SQL：使用 TRIM() 忽略空格

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

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

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

要搜索多个术语，您 可以 使用 = 和 OR（复数），如下所示：

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
  );

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

您还可以使用 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'
  );

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：根据位置筛选字符串的一部分

例如，要查找标题以“Small”开头的产品。

| 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：按多列筛选行

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

要获取结果必须 同时 为“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';

如果您想要产品 要么 是轻量级，要么 属于 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()

一般来说，只有当您无法使用上述方法筛选行时才使用正则表达式。正则表达式只对具有心灵感应能力的实体可读，并且数据库无法利用索引来加快查询时间。

SQL：处理空字符串与 NULL 值

空字符串（''）和 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，查询将仅返回轻量级产品。

查看 SQL 参数

下一步：SQL 日期筛选

学习 SQL 日期筛选：如何按日期筛选数据，从简单的精确匹配到像工作日和相对周期这样的复杂模式。

下一篇文章
© . All rights reserved.