How to Split a String into an Array in BigQuery
Last updated August 23, 2026 · By the SaturnSQL team
SPLIT(value, delimiter) returns an ARRAY<STRING>. Read elements with [OFFSET(0)] for 0-based access or [ORDINAL(1)] for 1-based access.
The delimiter defaults to a comma for STRING input. Splitting on an empty delimiter returns one element per character, and splitting an empty string returns an array with a single empty element rather than an empty array.
SELECT SPLIT('a,b,c') AS letters,
SPLIT('2026-08-23', '-') AS date_parts;Picking one piece out
OFFSET counts from 0 and ORDINAL counts from 1. Both raise an error when the index is past the end of the array, so reach for SAFE_OFFSET or SAFE_ORDINAL, which return NULL instead, whenever the input is not guaranteed to have that many parts.
SELECT SPLIT(full_name, ' ')[SAFE_OFFSET(0)] AS first_name,
SPLIT(path, '/')[SAFE_ORDINAL(2)] AS section
FROM users;One row per element
UNNEST turns the array into rows, which is what you want when a column holds a delimited list that should really have been a separate table.
SELECT id, TRIM(tag) AS tag
FROM articles, UNNEST(SPLIT(tags, ',')) AS tag;Back to a string
ARRAY_TO_STRING is the inverse, and takes an optional third argument used in place of NULL elements.
SELECT ARRAY_TO_STRING(['a', NULL, 'c'], ',', '?') AS joined;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free