按文本进行 SQL 筛选

学习 SQL 文本过滤:使用 WHERE、LIKE、IN、NOT IN、TRIM、UPPER、LOWER、正则表达式和变量来查找和过滤表中的字符串数据。

我们将涵盖的内容

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

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

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

SQL 按精确匹配过滤行

要搜索精确的、区分大小写的匹配项,请使用 WHERE 子句和 = 运算符。该术语应使用单引号。

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

如果您想排除所有产品,除了标题为“轻便羊毛电脑”的产品,请使用 !=

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

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 过滤包含部分文本的行

要根据列是否包含部分文本/字符串来过滤行,请使用 LIKE 关键字。例如,如果我们想搜索所有轻量级产品(区分大小写)

| 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”开头的产品。

| 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 按多列过滤行

您可以使用 ANDOR 在单个 WHERE 子句中按多列过滤。

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

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

您可以使用带 ANDWHERE 子句

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

使用括号,您可以巧妙地使用条件。以下是如何搜索属于 GizmoWidget 类别的轻量级产品。

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:REGEXPRLIKE,或 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 参数

延伸阅读

这有帮助吗?

感谢您的反馈!
分析师每周技巧
获取可行的见解
关于 AI 和数据的资讯,直接发送到您的收件箱
© . This site is unofficial and not affiliated with Metabase, Inc.