BigQuery中如何匹配MALE并排除FEMALE？生成性别标识字段

你遇到的核心问题：

• 用LIKE '%MALE%'会因MALE是FEMALE的子串导致误判
• 用UNNEST(SPLIT(genders, ";"))匹配失败，是因为拆分后的性别项带有前导空格（比如拆分"MALE; FEMALE"会得到["MALE", " FEMALE"]，第二个元素前的空格导致和"FEMALE"不匹配）

以下是三种可行解决方案：

方案1：修剪拆分元素后匹配 #

通过子查询修剪每个拆分出的字符串，再进行IN匹配：

SELECT
  genders,
  CASE WHEN 'MALE' IN (SELECT TRIM(g) FROM UNNEST(SPLIT(genders, ';')) g) THEN 1 ELSE 0 END AS gender_male,
  CASE WHEN 'FEMALE' IN (SELECT TRIM(g) FROM UNNEST(SPLIT(genders, ';')) g) THEN 1 ELSE 0 END AS gender_female
FROM your_table_name

方案2：正则表达式精确匹配完整项 #

利用正则边界匹配，确保只匹配独立的MALE或FEMALE项（处理分号前后的空格）：

SELECT
  genders,
  CASE WHEN REGEXP_CONTAINS(genders, r'(^|; )MALE(; |$)') THEN 1 ELSE 0 END AS gender_male,
  CASE WHEN REGEXP_CONTAINS(genders, r'(^|; )FEMALE(; |$)') THEN 1 ELSE 0 END AS gender_female
FROM your_table_name

正则说明：

• (^|; )：匹配字符串开头，或分号加空格的位置
• MALE/FEMALE：目标匹配值
• (; |$)：匹配分号加空格，或字符串结尾

方案3：用ARRAY_CONTAINS处理修剪后的数组 #

先将拆分并修剪的结果转为数组，再用ARRAY_CONTAINS判断：

SELECT
  genders,
  CASE WHEN ARRAY_CONTAINS(ARRAY(SELECT TRIM(g) FROM UNNEST(SPLIT(genders, ';')) g), 'MALE') THEN 1 ELSE 0 END AS gender_male,
  CASE WHEN ARRAY_CONTAINS(ARRAY(SELECT TRIM(g) FROM UNNEST(SPLIT(genders, ';')) g), 'FEMALE') THEN 1 ELSE 0 END AS gender_female
FROM your_table_name

这三种方法都能正确处理你的数据：仅含FEMALE的行中gender_male返回0、gender_female返回1；含MALE; FEMALE的行两个字段均返回1。

内容的提问来源于stack exchange，提问作者Lea C.