在BigQuery中基于另一列获取带分隔符字段内字符串的位置

解决方案 #

可以利用SPLIT将分隔字符串转为数组，再用ARRAY_POSITION函数直接获取目标元素的位置（该函数默认从1开始计数，正好符合需求）。

示例SQL #

SELECT
  Col1,
  Col2,
  ARRAY_POSITION(SPLIT(Col1, ' -> '), Col2) AS target_position
FROM
  your_table

逻辑说明 #

1. SPLIT(Col1, ' -> ')：将Col1按' -> '（注意包含前后空格）拆分为字符串数组，比如行1会生成["ACT", "BAT", "CAT", "DATE", "EAT"]，行2生成["CAT", "ACT", "EAT", "BAT", "DATE"]。
2. ARRAY_POSITION(数组, Col2)：查找Col2在数组中的第一个匹配位置，返回的索引从1开始，完全匹配你的期望输出。

为什么之前的方法无效 #

• regex_instr和instr：这两个函数是查找字符在字符串中的字节位置，而非分隔后元素的序号，所以不符合需求。
• 你尝试的array_length(split(regexp_extract(col1, col2), '->'))：regexp_extract只会提取Col2匹配的内容，而非Col2之前的所有部分，拆分后数组长度自然不对。

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