BigQuery string functions: syntax and tested examples

SPLIT

SPLIT(value, delimiter) returns an array, not a string. That is the reason most first attempts fail: you cannot compare or concatenate the result directly.

SELECT
  SPLIT('a,b,,c', ','),
  -- [a, b, , c]
  SPLIT('a,b,c', ',')[OFFSET(1)],
  -- b
  SPLIT('a,b,c', ',')[SAFE_OFFSET(9)],
  -- NULL
  ARRAY_LENGTH(SPLIT('', ','));
  -- 1

• OFFSET counts from 0, ORDINAL from 1.
• Use SAFE_OFFSET on real data. OFFSET(9) on a three-element array stops the query; SAFE_OFFSET(9) returns NULL.
• Two delimiters in a row produce an empty string, not a skipped element.
• Splitting an empty string returns an array with one empty element, so its length is 1, not 0.

To turn the pieces into rows, use UNNEST:

SELECT part
FROM UNNEST(SPLIT('utm_source=google&utm_medium=cpc', '&'))
  AS part;
-- utm_source=google
-- utm_medium=cpc

More on working with the result in array functions.

SUBSTR, LEFT and RIGHT

SUBSTR(value, position, length) counts positions from 1. A negative position counts from the end.

SELECT
  SUBSTR('BigQuery', 4),      -- Query
  SUBSTR('BigQuery', 1, 3),   -- Big
  SUBSTR('BigQuery', -5),     -- Query
  LEFT('BigQuery', 3),        -- Big
  RIGHT('BigQuery', 5);       -- Query

SUBSTRING is an alias for SUBSTR.

CONCAT and the || operator

SELECT
  CONCAT('order-', CAST(42 AS STRING)), -- order-42
  'a' || 'b',                           -- ab
  CONCAT('a', 'b', NULL);               -- NULL

One NULL argument makes the whole result NULL. This is the most common string bug in reports: a full name disappears because the middle name is missing. Wrap nullable parts in IFNULL(x, '') or COALESCE.

CONCAT needs strings, so cast numbers first. To join the values of an array, ARRAY_TO_STRING skips NULL elements by default:

SELECT
  ARRAY_TO_STRING(['a', NULL, 'c'], '-'),      -- a-c
  ARRAY_TO_STRING(['a', NULL, 'c'], '-', '?'); -- a-?-c

To join values across rows, use the aggregate STRING_AGG:

SELECT STRING_AGG(x, ', ' ORDER BY x)
FROM UNNEST(['b', 'a', 'c']) AS x;
-- a, b, c

LENGTH

SELECT
  LENGTH('héllo'),      -- 5
  BYTE_LENGTH('héllo'); -- 6

LENGTH counts characters. BYTE_LENGTH counts bytes, and an accented letter takes two. Use BYTE_LENGTH only when a size limit is in bytes.

Finding text: STRPOS, STARTS_WITH, CONTAINS_SUBSTR, LIKE

SELECT
  STRPOS('BigQuery', 'Query'),           -- 4
  STRPOS('BigQuery', 'x'),               -- 0
  INSTR('a-b-c', '-', 1, 2),             -- 4
  STARTS_WITH('BigQuery', 'Big'),        -- true
  ENDS_WITH('BigQuery', 'ry'),           -- true
  CONTAINS_SUBSTR('BigQuery', 'query'),  -- true
  'BigQuery' LIKE '%query%',             -- false
  'BigQuery' LIKE '%Query%';             -- true

• STRPOS returns 0 when the text is not found, not NULL and not -1.
• INSTR finds the n-th occurrence: above, the second hyphen.
• CONTAINS_SUBSTR ignores letter case. LIKE, STARTS_WITH and STRPOS do not. That one difference explains most “the filter returns nothing” problems. To make the others case-insensitive, compare LOWER(x).

REPLACE

SELECT REPLACE('2026-10-06', '-', '/');
-- 2026/10/06

REPLACE swaps every occurrence of an exact piece of text. It does not understand patterns; for those, use REGEXP_REPLACE below.

TRIM, LTRIM and RTRIM

SELECT
  TRIM('  hi  '),       -- hi
  TRIM('--hi--', '-'),  -- hi
  LTRIM('xxhi', 'x'),   -- hi
  RTRIM('hi!!', '!');   -- hi

Without a second argument, the functions remove whitespace. With one, they remove any of the listed characters from the ends. TRIM before every join on a text key that came from a spreadsheet or a form: a trailing space is invisible and breaks the match.

UPPER, LOWER and INITCAP

SELECT
  UPPER('hi'),               -- HI
  LOWER('Hi'),               -- hi
  INITCAP('hello world');    -- Hello World

LPAD and RPAD

SELECT
  LPAD('7', 3, '0'),      -- 007
  RPAD('ab', 5, '.'),     -- ab...
  LPAD('12345', 3, '0');  -- 123

The last line is worth noticing: when the value is longer than the target width, LPAD cuts it. A five-digit ID padded to three characters loses two digits without an error.

Regular expressions

BigQuery uses the RE2 syntax. Write patterns as raw strings, r'...', so backslashes do not need doubling.

SELECT
  REGEXP_CONTAINS('order-123', r'\d+'),
  -- true
  REGEXP_EXTRACT('order-123-us', r'(\d+)'),
  -- 123
  REGEXP_EXTRACT_ALL('a1b22c333', r'\d+'),
  -- [1, 22, 333]
  REGEXP_REPLACE('a  b   c', r'\s+', ' ');
  -- a b c

A practical one, the host from a URL:

SELECT REGEXP_EXTRACT(
  'https://www.owox.com/blog/articles/x?utm=1',
  r'^https?://([^/]+)');
-- www.owox.com

REGEXP_EXTRACT returns the first capture group, or the whole match if the pattern has none. It returns NULL when nothing matches. Regular expressions cost more to evaluate than plain functions, so where STARTS_WITH or REPLACE does the job, prefer it.

FORMAT, REVERSE and REPEAT

SELECT
  FORMAT('%s has %d orders', 'ana', 3),
  -- ana has 3 orders
  REVERSE('abc'),    -- cba
  REPEAT('ab', 3);   -- ababab

FORMAT builds a string from a pattern with placeholders and is easier to read than a long CONCAT.

Strings to other types

A string that holds a number or a date has to be converted before you can calculate with it. Use SAFE_CAST for numbers and SAFE.PARSE_DATE for dates; both return NULL for values that do not convert. See CAST and SAFE_CAST and date functions.

The behaviours that cause wrong results

Collected from the sections above, because each one fails silently:

• CONCAT with a NULL returns NULL.
• LIKE is case-sensitive; CONTAINS_SUBSTR is not.
• SPLIT returns an array, and OFFSET past the end is an error.
• STRPOS returns 0 for “not found”.
• LPAD truncates a value longer than the width.
• A trailing space makes two keys different.

Cleaning text once

String functions are mostly used for one job: turning text that arrived messy into keys and labels a report can group by. Campaign names get lower-cased, IDs padded, URLs cut down to a host. Each of those is a small decision, and the reports only agree with each other if every query makes it the same way.

That is the case for doing the cleaning in one query that the others read. In OWOX Data Marts that query is a Data Mart: your SQL with the TRIM, LOWER and REGEXP_EXTRACT you chose, saved with a description and an owner, and run in your warehouse. OWOX does not clean the text for you. It keeps the rules you wrote in one place, so a campaign is spelled the same in every report built on it.

Google BigQueryBigQuery Public Datasets in 2025: How to Use ThemSeptember 4, 2024→Google BigQueryMaster BigQuery SQL: The Ultimate 2025 GuideJune 28, 2024→Google BigQueryHow to connect BigQuery to Google Sheets: three waysOctober 6, 2026→

See all articles →