sqlgoogle-bigquerybq

Count the number of times a string appeared in a delimited field In BIGQUERY


I have a dataset with a delimiter "->" like:

ROW 1- "Q -> Res -> tes -> Res -> twet"
ROW 2- "rw -> gewg -> tes -> Res -> twet"
ROW 3- "Y -> Res -> Res -> Res -> twet"

I just want to count the number of "Res" in every row

Output would be:

ROW 1- 2
ROW 2- 1
ROW 3- 3

Ive tried writing the following queries but they arnt counting correctly or only counting once:

    countif(distinct(lower(FIELD_NAME) like '%Res%'))

    count(split(regexp_extract(FIELD_NAME, '(.*?)Res'), '->')) 

    (trim(Array_reverse(split(regexp_extract(FIELD_NAME, '(.*?)Res'),   '->')))

    count(regexp_extract(trim(FIELD_NAME), 'Res')) 

    count(regexp_contains(trim(FIELD_NAME), 'Res'))

Solution

  • Consider below

    select id, 
      ( select count(*)
        from unnest(split(text, ' -> ')) word
        where word = 'Res'
      ) cnt
    from your_table           
    

    if applied to sample data as in your question

    enter image description here

    output is

    enter image description here