6  必知必会第6课：用通配符进行过滤

作者

黄天元

6.1 数据库的连接

library(pacman)
p_load(tidyverse,DBI,RSQLite)

mydb = dbConnect(RSQLite::SQLite(), "data/TYSQL.sqlite")

6.2 基于SQL的数据操作

# 6.1.1
dbGetQuery(mydb,"SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';") 
  prod_id         prod_name
1  BNBG01 Fish bean bag toy
dbGetQuery(mydb,"SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';") 
  prod_id           prod_name
1  BNBG01   Fish bean bag toy
2  BNBG02   Bird bean bag toy
3  BNBG03 Rabbit bean bag toy
dbGetQuery(mydb,"SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';") 
          prod_name
1 Fish bean bag toy
# 6.1.2
dbGetQuery(mydb,"SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';")
  prod_id          prod_name
1    BR02 12 inch teddy bear
2    BR03 18 inch teddy bear
dbGetQuery(mydb,"SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '% inch teddy bear';")
  prod_id          prod_name
1    BR01  8 inch teddy bear
2    BR02 12 inch teddy bear
3    BR03 18 inch teddy bear
# 6.1.3
dbGetQuery(mydb,"SELECT cust_contact
FROM Customers
WHERE cust_contact GLOB '[JM]*'
ORDER BY cust_contact;")
    cust_contact
1      Jim Jones
2     John Smith
3 Michelle Green
dbGetQuery(mydb,"SELECT cust_contact
FROM Customers
WHERE cust_contact GLOB '[^JM]*'
ORDER BY cust_contact;")
        cust_contact
1 Denise L. Stephens
2         Kim Howard

6.3 SQLite GLOB 子句

SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。

6.4 基于tidyverse的数据操作

# 6.1.1
tbl(mydb,"Products") %>% 
  filter(str_like(prod_name,"Fish%")) %>% 
  select(prod_id, prod_name)
# Source:   SQL [1 x 2]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_id prod_name        
  <chr>   <chr>            
1 BNBG01  Fish bean bag toy
tbl(mydb,"Products") %>% 
  filter(prod_name %LIKE% "Fish%") %>% 
  select(prod_id, prod_name)
# Source:   SQL [1 x 2]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_id prod_name        
  <chr>   <chr>            
1 BNBG01  Fish bean bag toy
tbl(mydb,"Products") %>% 
  filter(prod_name %like% "Fish%") %>% 
  select(prod_id, prod_name)
# Source:   SQL [1 x 2]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_id prod_name        
  <chr>   <chr>            
1 BNBG01  Fish bean bag toy
tbl(mydb,"Products") %>% 
  filter(prod_name %like% "%bean bag%") %>% 
  select(prod_id, prod_name)
# Source:   SQL [3 x 2]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_id prod_name          
  <chr>   <chr>              
1 BNBG01  Fish bean bag toy  
2 BNBG02  Bird bean bag toy  
3 BNBG03  Rabbit bean bag toy
tbl(mydb,"Products") %>% 
  filter(prod_name %like% "F%y") %>% 
  select(prod_name)
# Source:   SQL [1 x 1]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_name        
  <chr>            
1 Fish bean bag toy
# 6.1.2
tbl(mydb,"Products") %>% 
  filter(prod_name %like% '__ inch teddy bear') %>% 
  select(prod_id, prod_name)
# Source:   SQL [2 x 2]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_id prod_name         
  <chr>   <chr>             
1 BR02    12 inch teddy bear
2 BR03    18 inch teddy bear
tbl(mydb,"Products") %>% 
  filter(prod_name %like% '% inch teddy bear') %>% 
  select(prod_id, prod_name)
# Source:   SQL [3 x 2]
# Database: sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
  prod_id prod_name         
  <chr>   <chr>             
1 BR01    8 inch teddy bear 
2 BR02    12 inch teddy bear
3 BR03    18 inch teddy bear
# 6.1.3
tbl(mydb,"Customers") %>% 
  filter(cust_contact %glob% '[JM]*') %>%  # GLOB
  arrange(cust_contact) %>% 
  select(cust_contact)
# Source:     SQL [3 x 1]
# Database:   sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
# Ordered by: cust_contact
  cust_contact  
  <chr>         
1 Jim Jones     
2 John Smith    
3 Michelle Green
tbl(mydb,"Customers") %>% 
  filter(cust_contact %glob% '[^JM]*') %>% 
  arrange(cust_contact) %>% 
  select(cust_contact)
# Source:     SQL [2 x 1]
# Database:   sqlite 3.46.0 [F:\Data\R4SQL\data\TYSQL.sqlite]
# Ordered by: cust_contact
  cust_contact      
  <chr>             
1 Denise L. Stephens
2 Kim Howard

6.5 注意

在 SQLite 中，LIKE 子句 不支持 [] 通配符（字符集匹配）。LIKE 的模式匹配规则仅支持两个通配符（%和_），而[] 通配符是 SQLite 中 GLOB 子句的专有通配符，用于指定一个字符集范围（或排除某些字符）。而 LIKE 仅能处理基于 SQL 标准的通配符，不能像 GLOB 那样使用字符集匹配。

6.5.1 LIKE 的通配符

SQLite 中的 LIKE 只支持两个标准通配符：

  • %：匹配零个或多个字符。
  • _：匹配单个字符。

6.5.1.1 示例：使用 LIKE 子句

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';  -- 匹配以 'Fish' 开头的所有产品

这个查询会匹配所有以 “Fish” 开头的产品名，例如 “Fish”, “Fishing”, “Fish123” 等。

6.5.2 GLOB 的通配符

与 LIKE 不同，GLOB 允许使用额外的通配符，包括 []，它们符合 Unix 文件系统的匹配规则。例如，[] 可以匹配指定字符集中的字符。

6.5.2.1 示例：使用 GLOB 子句

SELECT prod_name
FROM Products
WHERE prod_name GLOB 'Fish[123]';  -- 匹配 'Fish' 后面接 1、2 或 3 的产品名

这个查询会匹配 “Fish1”, “Fish2”, “Fish3” 等产品名。

6.5.3 LIKE 与 GLOB 的比较

特性 LIKE GLOB
支持的通配符 %（任意字符）
_（单个字符）		 *（任意字符）
?（单个字符）
[]（字符集）
区分大小写 通常不区分大小写（取决于数据库配置） 默认区分大小写
使用场景 模糊匹配，适合字符串的部分匹配 文件路径匹配或字符集匹配

6.5.4 如何使用 [] 匹配字符集

如果你想在 SQLite 中使用 [] 通配符进行字符集匹配，必须使用 GLOB 子句，而不是 LIKE。例如：

SELECT prod_name
FROM Products
WHERE prod_name GLOB 'Fish[123]';  -- 匹配 'Fish' 后面接 1、2 或 3 的产品名

如果你尝试在 LIKE 中使用 []，SQLite 将不会理解这个语法，并且会抛出一个错误或产生不预期的结果。

6.5.5 总结

  • LIKE 子句只能使用 % 和 _ 作为通配符，无法使用 [] 匹配字符集。
  • 如果需要使用 [] 来匹配字符集或范围，你需要使用 GLOB 子句。

6.6 练习

  • 请使用SQL和R两种方式，解决课后的挑战题。