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

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

Example Col1:*

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

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

I would like to lets extract the position of a value which is is ANOTHER COLUMN.

Example Col2:

Row 1| CAT

Row 2| ACT

Output would be -

Row 1| 3

Row 2| 2

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

Also tried this but it doesnt work:

select *, array_length(split(regexp_extract(col1, col2), '->'))

CodePudding user response：

How about this:

select col1_item, col2, (case when trim(col1_item) = trim(col2) then col2_index else null end) as col2_index_found
from (select col1_item, col2, col2_index
from 
(
  select split("ACT->BAT->CAT->DATE->EAT", "->")as col1, 'CAT' as col2  
union all 
  select split("CAT->ACT->EAT->BAT->DATE", "->")as col1, 'ACT' as col2 

), unnest(col1) as col1_item WITH OFFSET AS col2_index 
)

This will give what you want. Just one note: this offset is zero based index of array.

CodePudding user response：

Consider below approach

select *, 
  array_length(split(regexp_extract(col1, r'(.*?)' || col2), '->')) as position
from your_table

if applied to sample data in your question - output is

CodePudding user response：

Consider approach below using arrays:

with sample_data as (
  select "ACT->BAT->CAT->DATE->EAT" as col1, "CAT" as col2
  union all select "CAT->ACT->EAT->BAT->DATE" as col1, "ACT" as col2

),
split_col1 as (
select 
  split(col1, "->") as col1_arr,
  col2,
from sample_data
)
select  
  if(col2 = col1_arr[offset(index)], index 1, null) as col2_index
from split_col1,
  unnest(generate_array(0,array_length(col1_arr)-1)) as index
where col2 = col1_arr[offset(index)]

Output: