# 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.

Source: https://docs.querylab.io/bigquery/strings-and-collation/

Updated: 2026-10-14

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

**how strings compare**

```sql
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
```

Result in BigQuery (1 row):

| trailing_space_equal | upper_before_lower | equals_null | not_distinct_from_null | upper_sharp_s | sharp_s_equals_ss |
| --- | --- | --- | --- | --- | --- |
| false | true | NULL | false | STRASSE | false |

In Querylab.io, typing `WHERE status = '` lists real values from the column, so you see how each one is spelled and cased ([Value suggestions](https://docs.querylab.io/writing-sql/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](https://docs.querylab.io/bigquery/null-handling/).)

## Characters, bytes and normalization

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

**characters vs bytes**

```sql
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
```

Result in BigQuery (4 rows):

| w | characters | bytes | second_char |
| --- | --- | --- | --- |
| cafe | 4 | 4 | a |
| 😀 | 1 | 4 |  |
| café | 4 | 5 | a |
| 日本 | 2 | 6 | 本 |

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:

**the same word in two Unicode forms**

```sql
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
```

Result in BigQuery (1 row):

| equal_as_is | equal_normalized | composed_chars | decomposed_chars |
| --- | --- | --- | --- |
| false | true | 4 | 5 |

`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:

**binary, collation and LOWER**

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

Result in BigQuery (1 row):

| binary_equal | ci_equal | lower_equal |
| --- | --- | --- |
| false | true | true |

`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.

**functions that honor the collation**

```sql
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
```

Result in BigQuery (1 row):

| starts_with_app | position_of_PL | like_prefix | replaced |
| --- | --- | --- | --- |
| true | 3 | true | Abble |

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

**COUNT(DISTINCT) with and without 'und:ci'**

```sql
WITH signups AS (
  SELECT 'Ana@Example.com' AS email UNION ALL
  SELECT 'ana@example.com' UNION ALL
  SELECT 'ben@example.com'
)
SELECT COUNT(*) AS rows_total,
  COUNT(DISTINCT email) AS distinct_binary,
  COUNT(DISTINCT COLLATE(email, 'und:ci')) AS distinct_ci
FROM signups
```

Result in BigQuery (1 row):

| rows_total | distinct_binary | distinct_ci |
| --- | --- | --- |
| 3 | 3 | 2 |

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

**sort order with and without 'und:ci'**

```sql
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
```

Result in BigQuery (4 rows):

| fruit | binary_rank | ci_rank |
| --- | --- | --- |
| Apple | 1 | 1 |
| apple | 3 | 2 |
| banana | 4 | 3 |
| Zebra | 2 | 4 |

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 by column, table, dataset

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:

```sql
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:

**a collated column next to a plain one**

```sql
CREATE TEMP TABLE users (
  user_id INT64,
  email STRING COLLATE 'und:ci',
  city STRING
);
INSERT INTO users VALUES (1, 'Ana@Example.com', 'Lyon');

SELECT
  email = 'ana@example.com' AS email_matches,
  city = 'lyon' AS city_matches
FROM users
```

Result in BigQuery (1 row):

| email_matches | city_matches |
| --- | --- |
| true | false |

### 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:

**a language-specific collation**

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

BigQuery rejects this query:

```text
Collation 'en-US:ci' in collate function is not supported.
```

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:

**two collations in one comparison**

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

BigQuery rejects this query:

```text
Collation conflict: "und:cs" vs. "und:ci". Collation on argument 2 ("und:cs") in function = is not compatible with other arguments at [2:8]
```

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, '')`:

**REGEXP_CONTAINS on a collated value**

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

BigQuery rejects this query:

```text
Collation is not allowed on argument 1 ("und:ci"). Use COLLATE(arg, '') to remove collation at [1:8]
```

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`:

**the five common regex functions**

```sql
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
```

Result in BigQuery (1 row):

| is_get | order_id | param_names | masked | question_mark_at |
| --- | --- | --- | --- | --- |
| true | 1042 | [user, debug] | GET /orders/1042?user=*&debug=1 200 | 17 |

`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:

**regex finds substrings, LIKE matches the whole string**

```sql
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
```

Result in BigQuery (1 row):

| like_no_wildcard | like_prefix | underscore_matches_any_char | underscore_matches_X | regex_substring | regex_anchored |
| --- | --- | --- | --- | --- | --- |
| false | true | true | true | true | false |

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:

**LIKE ANY and LIKE ALL**

```sql
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
```

Result in BigQuery (4 rows):

| name | is_text | is_report_csv |
| --- | --- | --- |
| draft_report.csv | true | false |
| notes.txt | true | false |
| report_2026.csv | true | true |
| report_2026.parquet | false | false |

### 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:

**no group, one group, no match**

```sql
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
```

Result in BigQuery (1 row):

| no_group | one_group | no_match |
| --- | --- | --- |
| 1042 | 1042 | NULL |

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

**two capture groups**

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

BigQuery rejects this query:

```text
Regular expressions passed into extraction functions must not have more than 1 capturing group
```

`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:

**'\d' in a normal string**

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

BigQuery rejects this query:

```text
Syntax error: Illegal escape sequence: \d at [1:38]
```

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

**r'\d+' and '\\d+'**

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

Result in BigQuery (1 row):

| raw_string | escaped_backslash |
| --- | --- |
| 1042 | 1042 |

### What RE2 lacks

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

**lookahead in a pattern**

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

BigQuery rejects this query:

```text
Cannot parse regular expression: invalid perl operator: (?=
```

`(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:

**SPLIT edge cases**

```sql
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
```

Result in BigQuery (1 row):

| three | empty_middle | empty_input | no_delimiter_found | by_character | null_input |
| --- | --- | --- | --- | --- | --- |
| 3 | 3 | 1 | 1 | [a, b, c] | NULL |

To get one row per piece, `UNNEST` the result. See [UNNEST](https://docs.querylab.io/bigquery/unnest/).

## SUBSTR and STRPOS count from 1

**1-based positions**

```sql
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
```

Result in BigQuery (1 row):

| from_2_length_3 | last_3 | start_0 | first_l | missing | second_l |
| --- | --- | --- | --- | --- | --- |
| ell | llo | he | 3 | 0 | 4 |

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:

**format specifiers and %t vs %T**

```sql
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
```

Result in BigQuery (1 row):

| counts | padding | numbers | dates | strings | null_values |
| --- | --- | --- | --- | --- | --- |
| 1234 orders,  87.5% paid | ab\|     right\|left      \| | 00042\|+42\|ff | 2026-03-01 \| DATE "2026-03-01" | it's \| it's \| "it's" | NULL \| NULL |

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`.

Try an RE2 pattern with REGEXP_EXTRACT. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [String functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions)

- [Collation](https://cloud.google.com/bigquery/docs/reference/standard-sql/collation-concepts)

- [Operators, LIKE](https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#like_operator)