Strings, regular expressions and collation in BigQuery

BigQuery compares strings byte for byte, so Apple and apple differ and Z sorts before a. Regular expressions use RE2 (no lookahead, no backreferences), and the only collation is 'und:ci' for case-insensitive comparison.

A BigQuery STRING is a sequence of Unicode characters stored as UTF-8. Comparison is by code point: 'Apple' = 'apple' is false, 'Z' < 'a' is true, and trailing spaces count. Regular expression functions use Google’s RE2 engine. For case-insensitive comparison, either lower-case both sides or give a string the collation 'und:ci', which is the only collation BigQuery supports.

Comparison is binary

SELECT
  'a ' = 'a' AS trailing_space_equal,
  'Z' < 'a' AS upper_before_lower,
  'a' = CAST(NULL AS STRING) AS equals_null,
  'a' IS NOT DISTINCT FROM CAST(NULL AS STRING) AS not_distinct_from_null,
  UPPER('straße') AS upper_sharp_s,
  'ß' = 'SS' AS sharp_s_equals_ss

In Querylab.io, typing WHERE status = ' lists real values from the column, so you see how each one is spelled and cased (Value suggestions).

'a ' and 'a' are different, uppercase letters sort before lowercase ones, and 'ß' is not equal to 'SS', although UPPER('straße') gives STRASSE. Comparing with a NULL string gives NULL, and IS NOT DISTINCT FROM treats NULL as a value. (A bare literal NULL on one side of = is an error, see NULL handling.)

Characters, bytes and normalization

LENGTH counts characters (code points), BYTE_LENGTH counts the UTF-8 bytes. SUBSTR and STRPOS count characters too:

WITH words AS (
  SELECT 'cafe' AS w UNION ALL SELECT 'café' UNION ALL SELECT '日本' UNION ALL SELECT '😀'
)
SELECT w, LENGTH(w) AS characters, BYTE_LENGTH(w) AS bytes, SUBSTR(w, 2, 1) AS second_char
FROM words
ORDER BY bytes, w

Code points are not what a reader sees as one letter. é can be one code point or e plus a combining accent. Both look the same, and BigQuery says they are different:

WITH names AS (
  SELECT 'café' AS composed, 'café' AS decomposed
)
SELECT
  composed = decomposed AS equal_as_is,
  NORMALIZE(composed) = NORMALIZE(decomposed) AS equal_normalized,
  LENGTH(composed) AS composed_chars,
  LENGTH(decomposed) AS decomposed_chars
FROM names

NORMALIZE(x) converts to a canonical form (NFC by default; NFD, NFKC and NFKD are options) so such strings compare equal. NORMALIZE_AND_CASEFOLD(x) also folds case, which is the right function for a case-insensitive key that must treat Straße and STRASSE alike: it returns strasse.

Case-insensitive comparison

LOWER(a) = LOWER(b) works anywhere. The alternative is a collation, which makes the comparison itself case-insensitive:

SELECT
  'Apple' = 'apple' AS binary_equal,
  COLLATE('Apple', 'und:ci') = 'apple' AS ci_equal,
  LOWER('Apple') = LOWER('apple') AS lower_equal

COLLATE(value, 'und:ci') attaches the collation to a value. The collation then follows the value through functions such as CONCAT, LOWER, SPLIT and SUBSTR, and it applies to =, <, IN, LIKE, JOIN, GROUP BY, ORDER BY, COUNT(DISTINCT), MIN, MAX, STARTS_WITH, STRPOS and REPLACE, among others.

SELECT
  STARTS_WITH(COLLATE('Apple', 'und:ci'), 'app') AS starts_with_app,
  STRPOS(COLLATE('Apple', 'und:ci'), 'PL') AS position_of_PL,
  COLLATE('Apple', 'und:ci') LIKE 'a%' AS like_prefix,
  REPLACE(COLLATE('Apple', 'und:ci'), 'PP', 'bb') AS replaced

Grouping and distinct counts follow it as well. Two emails that differ only by case count as one:

WITH signups AS (
  SELECT '[email protected]' AS email UNION ALL
  SELECT '[email protected]' UNION ALL
  SELECT '[email protected]'
)
SELECT COUNT(*) AS rows_total,
  COUNT(DISTINCT email) AS distinct_binary,
  COUNT(DISTINCT COLLATE(email, 'und:ci')) AS distinct_ci
FROM signups

ORDER BY with the collation sorts apple and Apple next to each other, and Zebra goes last instead of second:

WITH names AS (
  SELECT 'apple' AS fruit UNION ALL SELECT 'Zebra' UNION ALL SELECT 'Apple' UNION ALL SELECT 'banana'
)
SELECT fruit, ROW_NUMBER() OVER (ORDER BY fruit) AS binary_rank,
  ROW_NUMBER() OVER (ORDER BY COLLATE(fruit, 'und:ci'), fruit) AS ci_rank
FROM names
ORDER BY ci_rank

The tie between Apple and apple is not defined by the collation, so the query adds the plain column as a second sort key.

Collation on columns, tables and datasets

You can set a collation on a column, a table or a dataset. A new column gets the table’s default, a new table gets the dataset’s default, and a collation on a column wins over both. A column declared with the empty collation COLLATE '' opts out. These statements aren’t run on the page. We ran the table statements on BigQuery, and the dataset one follows Google’s reference:

CREATE SCHEMA mydataset DEFAULT COLLATE 'und:ci';

CREATE TABLE mydataset.users (
  email STRING,              -- inherits 'und:ci' from the table
  note STRING COLLATE ''     -- binary comparison
) DEFAULT COLLATE 'und:ci';

ALTER TABLE mydataset.users SET DEFAULT COLLATE 'und:ci';

On a table created this way, email = 'x' matched 'X' and note = 'x' did not. A column added later with ALTER TABLE ... ADD COLUMN also inherited the default. SET DEFAULT COLLATE changes only columns added afterwards: on a table altered this way the existing column still compared in binary and the new one was case-insensitive. Here is the same effect on a temp table:

CREATE TEMP TABLE users (
  user_id INT64,
  email STRING COLLATE 'und:ci',
  city STRING
);
INSERT INTO users VALUES (1, '[email protected]', 'Lyon');

SELECT
  email = '[email protected]' AS email_matches,
  city = 'lyon' AS city_matches
FROM users

What the collation can’t do

'und:ci' is the only specification. 'und:cs', or a language such as 'en-US:ci', is an error:

SELECT COLLATE('Apple', 'en-US:ci') AS fruit

Two different collation specifications in one comparison conflict. Comparing und:ci with und:cs is reported as a “Collation conflict”, before BigQuery gets to rejecting und:cs itself:

SELECT COLLATE('Apple', 'und:ci') = COLLATE('apple', '') AS same,
       COLLATE('Apple', 'und:ci') = COLLATE('apple', 'und:cs') AS conflict

A value with no collation, or the empty one, combines with a collated value and takes its collation.

The regular expression functions refuse collated input. The error tells you the fix, which is COLLATE(value, ''):

SELECT REGEXP_CONTAINS(COLLATE('Apple', 'und:ci'), 'apple') AS matched

For case-insensitive regex, use the (?i) flag instead.

Regular expressions use RE2

The regex functions are REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_EXTRACT_ALL, REGEXP_REPLACE, REGEXP_INSTR and REGEXP_SUBSTR:

WITH logs AS (SELECT 'GET /orders/1042?user=ana&debug=1 200' AS line)
SELECT
  REGEXP_CONTAINS(line, r'^GET ') AS is_get,
  REGEXP_EXTRACT(line, r'/orders/(\d+)') AS order_id,
  REGEXP_EXTRACT_ALL(line, r'(\w+)=') AS param_names,
  REGEXP_REPLACE(line, r'user=\w+', 'user=*') AS masked,
  REGEXP_INSTR(line, r'\?') AS question_mark_at
FROM logs

REGEXP_CONTAINS is unanchored, so it finds a match anywhere in the string, while LIKE must match the whole string, with % for any run of characters and _ for exactly one:

SELECT
  'orders_2026' LIKE 'orders' AS like_no_wildcard,
  'orders_2026' LIKE 'orders%' AS like_prefix,
  'orders_2026' LIKE 'orders_2026' AS underscore_matches_any_char,
  'ordersX2026' LIKE 'orders_2026' AS underscore_matches_X,
  REGEXP_CONTAINS('orders_2026', r'orders') AS regex_substring,
  REGEXP_CONTAINS('orders_2026', r'^orders$') AS regex_anchored

Use ^ and $ to anchor a regex. In LIKE, _ is a wildcard, so a table named orders_2026 also matches ordersX2026. Escape it as \_ in a raw string to match a literal underscore. LIKE ANY (...) and LIKE ALL (...) take a list of patterns:

WITH files AS (
  SELECT 'report_2026.csv' AS name UNION ALL
  SELECT 'report_2026.parquet' UNION ALL
  SELECT 'draft_report.csv' UNION ALL
  SELECT 'notes.txt'
)
SELECT
  name,
  name LIKE ANY ('%.csv', '%.txt') AS is_text,
  name LIKE ALL ('report%', '%.csv') AS is_report_csv
FROM files
ORDER BY name

Capture groups in REGEXP_EXTRACT

REGEXP_EXTRACT returns the whole match when the pattern has no group, and the group’s text when it has one. No match gives NULL:

SELECT
  REGEXP_EXTRACT('order 1042 shipped', r'\d+') AS no_group,
  REGEXP_EXTRACT('order 1042 shipped', r'order (\d+)') AS one_group,
  REGEXP_EXTRACT('order 1042 shipped', r'refund (\d+)') AS no_match

More than one capture group is an error. Use (?:...) for groups you don’t want captured:

SELECT REGEXP_EXTRACT('order 1042 shipped', r'(\d+) (\w+)') AS two_groups

REGEXP_REPLACE takes the groups in the replacement as \1, \2, and \0 for the whole match.

Escaping: use raw strings

In an ordinary string literal, '\d' is an invalid escape sequence, and BigQuery refuses it before the regex runs:

SELECT REGEXP_EXTRACT('order 1042', '\d+') AS order_id

Write the pattern as a raw string, r'\d+', or double the backslash:

SELECT
  REGEXP_EXTRACT('order 1042', r'\d+') AS raw_string,
  REGEXP_EXTRACT('order 1042', '\\d+') AS escaped_backslash

What RE2 lacks

RE2 matches in linear time and gives that up by leaving out lookahead, lookbehind and backreferences. (?=...) fails at parse time:

SELECT REGEXP_CONTAINS('price: 12 USD', r'\d+(?= USD)') AS has_usd_price

(a)\1 fails the same way (“invalid escape sequence: \1”). The flag (?i) for case-insensitive matching works. A pattern that RE2 can’t parse is an error when the input is a real string, even an empty one. With a NULL input the function returns NULL without ever parsing the pattern, so a bad pattern can hide until the first non-NULL row.

SPLIT

SPLIT(value, delimiter) returns an array. Without a match you get one element, an empty string gives one empty element, and a NULL input gives a NULL array. The default delimiter for STRING is a comma. An empty delimiter splits into characters:

SELECT
  ARRAY_LENGTH(SPLIT('a,b,c', ',')) AS three,
  ARRAY_LENGTH(SPLIT('a,,c', ',')) AS empty_middle,
  ARRAY_LENGTH(SPLIT('', ',')) AS empty_input,
  ARRAY_LENGTH(SPLIT('a,b', ';')) AS no_delimiter_found,
  SPLIT('abc', '') AS by_character,
  ARRAY_LENGTH(SPLIT(CAST(NULL AS STRING), ',')) AS null_input

To get one row per piece, UNNEST the result. See UNNEST.

SUBSTR and STRPOS count from 1

SELECT
  SUBSTR('hello', 2, 3) AS from_2_length_3,
  SUBSTR('hello', -3) AS last_3,
  SUBSTR('hello', 0, 2) AS start_0,
  STRPOS('hello', 'l') AS first_l,
  STRPOS('hello', 'z') AS missing,
  INSTR('hello', 'l', 1, 2) AS second_l

A start of 0 is treated like 1 (SUBSTR('hello', 0, 2) is he), a negative start counts from the end, and STRPOS returns 0 when nothing is found, not NULL and not -1.

FORMAT

FORMAT is printf-style. %d, %f, %s and %x work as in C, with width, precision and flags like %05d and %-10s. %t prints a value as text and %T prints it as a SQL literal, which is the one to use when you build a query string:

SELECT
  FORMAT('%d orders, %5.1f%% paid', 1234, 87.456) AS counts,
  FORMAT('%s|%10s|%-10s|', 'ab', 'right', 'left') AS padding,
  FORMAT('%05d|%+d|%x', 42, 42, 255) AS numbers,
  FORMAT('%t | %T', DATE '2026-03-01', DATE '2026-03-01') AS dates,
  FORMAT('%s | %t | %T', "it's", "it's", "it's") AS strings,
  FORMAT('%t | %T', CAST(NULL AS STRING), CAST(NULL AS STRING)) AS null_values

The ' flag adds thousands separators: FORMAT("%'d", 1234567) is 1,234,567 (use double quotes around the format string). A NULL argument to %d or %s makes the whole result NULL, while %t and %T print the text NULL.