在BigQuery中获取带分隔符字段内字符串的位置

要解决按->（含前后空格）分隔符获取指定单词位置的问题，你可以通过拆分字符串为单词数组，再定位目标元素的方式实现，以下是两种实用方法：

方法一：SPLIT + ARRAY_POSITION 直接定位 #

利用SPLIT将字符串拆分为单词数组，再用ARRAY_POSITION返回目标单词在数组中的位置（BigQuery数组索引从1开始，正好匹配需求的位置计数）：

WITH sample_data AS (
  SELECT "ACT -> BAT -> CAT -> DATE -> EAT" AS str UNION ALL
  SELECT "CAT -> ACT -> EAT -> BAT -> DATE" AS str
)
SELECT
  str,
  ARRAY_POSITION(SPLIT(str, ' -> '), 'CAT') AS cat_position
FROM sample_data;

执行后输出：

方法二：UNNEST WITH OFFSET 灵活处理场景 #

如果需要应对更复杂的情况（比如同一单词多次出现、过滤空值等），可以用UNNEST结合偏移量来计算位置：

WITH sample_data AS (
  SELECT "ACT -> BAT -> CAT -> DATE -> EAT" AS str UNION ALL
  SELECT "CAT -> ACT -> EAT -> BAT -> DATE" AS str
)
SELECT
  str,
  MAX(IF(word = 'CAT', offset + 1, NULL)) AS cat_position
FROM sample_data,
UNNEST(SPLIT(str, ' -> ')) AS word WITH OFFSET
GROUP BY str;

这里OFFSET返回的是从0开始的索引，所以需要加1转为从1开始的位置，MAX(IF(...))用来筛选出目标单词对应的位置值。

为什么之前的方法无效？ #

regex_instr和instr函数是基于字符在字符串中的绝对位置计算的，而非按分隔符分割后的单词位置，因此无法直接得到你需要的结果。必须先将字符串拆分为独立的单词集合，再进行位置定位。

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