如何在BigQuery中查询以指定字符开头的名称？附A/G/E开头国家示例

嘿，这两个关于BigQuery字符串匹配的问题很常见，我结合你提供的示例数据集来给你详细解答：

在BigQuery里，有两种常用的方法可以实现这个需求：

• 方法一：使用STARTS_WITH()函数（BigQuery原生函数，语义更直观）
  这个函数专门用来判断字符串是否以指定前缀开头，语法简单易懂：STARTS_WITH(字段名, '前缀')

  比如要从你的示例表中找出以"A"开头的国家名称，查询语句可以写：

  #standardSQL
  with table1 as(
   select "America" as country_name
   union all
   select "Germany" as country_name
   union all
   select "England" as country_name
   union all
   select "Nauru" as country_name
   union all
   select "Brunei" as country_name
   union all
   select "Kiribati" as country_name
   union all
   select "Djibouti" as country_name
   union all
   select "Malta" as country_name
  )
  select country_name 
  from table1
  where STARTS_WITH(country_name, 'A');
  

• 方法二：使用LIKE操作符（标准SQL语法，兼容性更广）
  用LIKE '前缀%'的形式，%是通配符，表示任意长度的后续字符：

  #standardSQL
  with table1 as(
   select "America" as country_name
   union all
   select "Germany" as country_name
   union all
   select "England" as country_name
   union all
   select "Nauru" as country_name
   union all
   select "Brunei" as country_name
   union all
   select "Kiribati" as country_name
   union all
   select "Djibouti" as country_name
   union all
   select "Malta" as country_name
  )
  select country_name 
  from table1
  where country_name LIKE 'A%';
  

针对多前缀的情况，同样可以用几种方式实现：

• 方法一：STARTS_WITH结合多条件判断
  用OR连接多个STARTS_WITH语句，明确指定每个前缀：

  #standardSQL
  with table1 as(
   select "America" as country_name
   union all
   select "Germany" as country_name
   union all
   select "England" as country_name
   union all
   select "Nauru" as country_name
   union all
   select "Brunei" as country_name
   union all
   select "Kiribati" as country_name
   union all
   select "Djibouti" as country_name
   union all
   select "Malta" as country_name
  )
  select country_name 
  from table1
  where STARTS_WITH(country_name, 'A') 
     OR STARTS_WITH(country_name, 'G') 
     OR STARTS_WITH(country_name, 'E');
  

• 方法二：使用正则表达式REGEXP_CONTAINS
  正则里用^[AGE]表示以A、G、E中的任意一个开头，写法更简洁：

  #standardSQL
  with table1 as(
   select "America" as country_name
   union all
   select "Germany" as country_name
   union all
   select "England" as country_name
   union all
   select "Nauru" as country_name
   union all
   select "Brunei" as country_name
   union all
   select "Kiribati" as country_name
   union all
   select "Djibouti" as country_name
   union all
   select "Malta" as country_name
  )
  select country_name 
  from table1
  where REGEXP_CONTAINS(country_name, r'^[AGE]');
  

• 方法三：多条件LIKE判断
  和STARTS_WITH的思路类似，用OR连接多个LIKE语句：

  #standardSQL
  with table1 as(
   select "America" as country_name
   union all
   select "Germany" as country_name
   union all
   select "England" as country_name
   union all
   select "Nauru" as country_name
   union all
   select "Brunei" as country_name
   union all
   select "Kiribati" as country_name
   union all
   select "Djibouti" as country_name
   union all
   select "Malta" as country_name
  )
  select country_name 
  from table1
  where country_name LIKE 'A%' 
     OR country_name LIKE 'G%' 
     OR country_name LIKE 'E%';
  

以上几种方法运行后，都会返回America、Germany、England这三个符合条件的国家名称~

内容的提问来源于stack exchange，提问作者user475043