按文本进行 SQL 筛选
学习 SQL 文本过滤:使用 WHERE、LIKE、IN、NOT IN、TRIM、UPPER、LOWER、正则表达式和变量来查找和过滤表中的字符串数据。
我们将涵盖的内容
- 使用
=
过滤完全匹配的行(或使用!=
排除)› - 使用
UPPER()
、LOWER()
和TRIM()
忽略大小写和空格 › - 使用
IN
和NOT IN
包含或排除行 › - 使用
LIKE
和通配符查找部分匹配项 › - 使用
SUBSTRING()
按位置匹配 › - 使用
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
如果您想排除所有产品,除了标题为“轻便羊毛电脑”的产品,请使用 !=
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 按多列过滤行
您可以使用 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';
如果您想要行,其中产品是轻量级的或属于 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 参数