Datawise
Practical SQL

Splitting a STRING in BigQuery

Constantin LunguUpdated 1 min read

Photo by Bon Vivant on Unsplash

Splitting a string in BigQuery works pretty much the same as in Excel.

SPLIT works in a similar way as its Excel cousin TEXTSPLIT - taking a string to be split and a delimiter (can be multiple characters), and returns an array of elements.

You can access them using the 0-based index or check out my previous post on more options for accessing array elements in BigQuery.

SELECT
  SPLIT('a, b, c', ', ')[0] AS first_element,
  SPLIT('a, b, c', ', ')[1] AS second_element,
  SPLIT('a, b, c', ', ')[2] AS third_element

BigQuery results with first_element a, second_element b and third_element c, beside Excel, where =TEXTSPLIT(B1,", ") splits the text a, b, c into cells a, b and c.