Get Position of a String in a field with delimiters BigQuery-CodePudding

I want to get the position of a word in a field that has the following data with the delimiter as "->":

Example:

Row 1| "ACT -> BAT -> CAT -> DATE -> EAT"

Row 2| "CAT -> ACT -> EAT -> BAT -> DATE"

I would like to lets say extract the position of CAT in each row.

Output would be -

Row 1| 3

Row 2| 1

Ive tried regex_instr and instr but they both return position of the alphabet i think not the word

CodePudding user response：

Consider below

select *, 
  array_length(split(regexp_extract(col, r'(.*?)CAT'), '->')) as position
from your_table

if applied to sample data in your question - output is