Using INSTR in BigQuery

If you ever need to do something different based on the existence of a particular substring in BigQuery, take a look at the INSTR function.

It returns the 1-based index of the first occurrence of a substring (1 or more characters) in another STRING. The function returns 0 if the substring was not found.

There's also a possibility to specify at what position to start the search (like in good old Excel) and which occurrence to get.

WITH input_data AS (
  SELECT 1 AS user_id, 'apple,grapes,melon' AS fruits
  UNION ALL
  SELECT 2 AS user_id, 'pear;mango;kiwi'
  UNION ALL
  SELECT 3 AS user_id, 'banana'
)

SELECT

  user_id,
  fruits,
  INSTR(fruits,',') AS comma_first_position,
  INSTR(fruits,';') AS semicolon_first_position,

  CASE WHEN INSTR(fruits,';') > 0 THEN SPLIT(fruits,',')
       ELSE SPLIT(fruits,',') END AS fruits_array,

FROM input_data