Collation

January 30, 2026 · View on GitHub

GoogleSQL supports collation. Collation defines rules to sort and compare strings in certain operations, such as conditional expressions, joins, and groupings.

By default, GoogleSQL sorts strings case-sensitively. This means that a and A are treated as different letters, and Z would come before a.

Example default sorting: Apple, Zebra, apple

By contrast, collation lets you sort and compare strings case-insensitively or according to specific language rules.

Example case-insensitive collation: Apple, apple, Zebra

To customize collation for a collation-supported operation, you typically assign a collation specification to at least one string in the operation inputs. Some operations can't use collation, but can propagate collation through them.

Collation is useful when you need fine-tuned control over how values are sorted, joined, or grouped in tables.

Operations affected by collation

The following example query operations are affected by collation when sorting and comparing strings:

Operations
Collation-supported comparison operations
Join operations
`ORDER BY`
`GROUP BY`
`WINDOW` for window functions
Collation-supported scalar functions
Collation-supported aggregate functions
Set operations
`NULLIF` conditional expression

Operations that propagate collation

Collation can pass through some query operations to other parts of a query. When collation passes through an operation in a query, this is known as propagation. During propagation:

If an input contains no collation specification or an empty collation specification and another input contains an explicitly defined collation, the explicitly defined collation is used for all of the inputs.
All inputs with a non-empty explicitly defined collation specification must have the same type of collation specification, otherwise an error is thrown.

GoogleSQL has several functions, operators, and expressions that can propagate collation.

In the following example, the 'und:ci' collation specification is propagated from the character column to the ORDER BY operation.

-- With collation
SELECT *
FROM UNNEST([
  COLLATE('B', 'und:ci'),
  'b',
  'a'
]) AS character
ORDER BY character

/*-----------+
 | character |
 +-----------+
 | a         |
 | B         |
 | b         |
 +-----------*/

-- Without collation
SELECT *
FROM UNNEST([
  'B',
  'b',
  'a'
]) AS character
ORDER BY character

/*-----------+
 | character |
 +-----------+
 | B         |
 | a         |
 | b         |
 +-----------*/

Functions

The following example functions propagate collation.

Function	Notes
`AEAD.DECRYPT_STRING`
`ANY_VALUE`
`ARRAY_AGG`	Collation on input arguments are propagated as collation on the array element.
`ARRAY_FIRST`
`ARRAY_LAST`
`ARRAY_SLICE`
`ARRAY_TO_STRING`	Collation on array elements are propagated to output.
`COLLATE`
`CONCAT`
`FORMAT`	Collation from `format_string` to the returned string is propagated.
`FORMAT_DATE`	Collation from `format_string` to the returned string is propagated.
`FORMAT_DATETIME`	Collation from `format_string` to the returned string is propagated.
`FORMAT_TIME`	Collation from `format_string` to the returned string is propagated.
`FORMAT_TIMESTAMP`	Collation from `format_string` to the returned string is propagated.
`FROM_PROTO`
`GREATEST`
`LAG`
`LEAD`
`LEAST`
`LEFT`
`LOWER`
`LPAD`
`MAX`
`MIN`
`NET.HOST`
`NET.MAKE_NET`
`NET.PUBLIC_SUFFIX`
`NET.REG_DOMAIN`
`NTH_VALUE`
`NORMALIZE`
`NORMALIZE_AND_CASEFOLD`
`NULLIFERROR`
`PROTO_DEFAULT_IF_NULL`
`REPEAT`
`REPLACE`
`REVERSE`
`RIGHT`
`RPAD`
`SOUNDEX`
`SPLIT`	Collation on input arguments are propagated as collation on the array element.
`STRING_AGG`
`SUBSTR`
`UPPER`

Operators

The following example operators propagate collation.

Operator	Notes
`\|\|` concatenation operator
Array subscript operator	Propagated to output.
Set operators	Collation of an output column is decided by the collations of input columns at the same position.
`STRUCT` field access operator	When getting a `STRUCT`, collation on the `STRUCT` field is propagated as the output collation.
`UNNEST`	Collation on the input array element is propagated to output.

Expressions

The following example expressions propagate collation.

Expression	Notes
`ARRAY`	When you construct an `ARRAY`, collation on input arguments is propagated on the elements in the `ARRAY`.
`CASE`
`CASE` expr
`COALESCE`
`IF`
`IFNULL`
`NULLIF`
`STRUCT`	When you construct a `STRUCT`, collation on input arguments is propagated on the fields in the `STRUCT`.

Where you can assign a collation specification

You can assign a collation specification to these collation-supported types:

A STRING
A STRING field in a STRUCT
A STRING element in an ARRAY

In addition:

You can assign a default collation specification to a schema when you create or alter it. This assigns a default collation specification to all future tables that are added to the schema if the tables don't have their own default collation specifications.
You can assign a default collation specification to a table when you create or alter it. This assigns a collation specification to all future collation-supported columns that are added to the table if the columns don't have collation specifications. This overrides a default collation specification on a schema.
You can assign a collation specification to a collation-supported type in a column. A column that contains a collation-supported type in its column schema is a collation-supported column. This overrides a default collation specification on a table.
You can assign a collation specification to a collation-supported query operation.
You can assign a collation specification to a collation-supported expression with the COLLATE function. This overrides any collation specifications set previously.

In summary:

You can define a default collation specification for a schema. For example:

CREATE SCHEMA (...)
DEFAULT COLLATE 'und:ci'

You can define a default collation specification for a table. For example:

CREATE TABLE (...)
DEFAULT COLLATE 'und:ci'

You can define a collation specification for a collation-supported column. For example:

CREATE TABLE (
  case_insensitive_column STRING COLLATE 'und:ci'
)

You can specify a collation specification for a collation-supported expression with the COLLATE function. For example:

SELECT COLLATE('a', 'und:ci') AS character

In the ORDER BY clause, you can specify a collation specification for a collation-supported column. This overrides any collation specifications set previously.

For example:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "und:ci"

DDL statements

You can assign a collation specification to the following DDL statements.

Location	Support	Notes
Schema	`CREATE SCHEMA`	Create a schema and optionally add a default
: : : collation specification to the schema. :
Schema	`ALTER SCHEMA`	Updates the default collation specification
: : : for a schema. :
Table	`CREATE TABLE`	Create a table and optionally add a default
: : : collation specification to a table :
: : : or a collation specification to a :
: : : collation-supported type in a column. :
: : : :
: : : You can't have collation on a column used :
: : : with `CLUSTERING`. :
Table	`ALTER TABLE`	Update the default collation specification
: : : for collation-supported type in a table. :
Column	`ADD COLUMN`	Add a collation specification to a
: : : collation-supported type in a new column :
: : : in an existing table. :

Data types

You can assign a collation specification to the following data types.

Type	Notes
`STRING`	You can apply a collation specification directly to
: : this data type. :
`STRUCT`	You can apply a collation specification to a
: : `STRING` field in a `STRUCT`. A `STRUCT` can :
: : have `STRING` fields with different :
: : collation specifications. :
: : A `STRUCT` can only be used in comparisons with the :
: : following operators and conditional expressions::
: : `=`, `!=`, `IN`, `NULLIF`, and `CASE`. :
`ARRAY`	You can apply a collation specification to a
: : `STRING` element in an `ARRAY`. An `ARRAY` can :
: : have `STRING` elements with different :
: : collation specifications. :

Note: Use the COLLATE function to apply a collation specification to collation-supported expressions.

Query statements

You can assign a collation specification to the following query statements.

Type	Support
Sorting	`ORDER BY` clause

Functions, operators, and conditional expressions

You can assign a collation specification to the following functions, operators, and conditional expressions.

Functions

Type	Support	Notes
Scalar	`COLLATE`
Scalar	`ENDS_WITH`
Scalar	`GREATEST`
: : : :
Scalar	`INSTR`
Scalar	`LEAST`
Scalar	`REPLACE`
: : : :
Scalar	`SPLIT`
Scalar	`STARTS_WITH`
Scalar	`STRPOS`
Aggregate	`COUNT`	This operator is only affected by
: : : collation when the input includes :
: : : the `DISTINCT` argument. :
Aggregate	`MAX`
Aggregate	`MIN`
: : : :

Operators

Support	Notes
`<`
`<=`
`>`
`>=`
`=`
`!=`
`[NOT] BETWEEN`
`[NOT] IN`	Limitations apply.
`[NOT] LIKE`	Limitations apply.
Quantified `[NOT] LIKE`	Limitations apply.

Conditional expressions

Support
`CASE`
`CASE` expr
`NULLIF`

The preceding collation-supported operations (functions, operators, and conditional expressions) can include input with explicitly defined collation specifications for collation-supported types. In a collation-supported operation:

All inputs with a non-empty, explicitly defined collation specification must be the same, otherwise an error is thrown.
If an input doesn't contain an explicitly defined collation and another input contains an explicitly defined collation, the explicitly defined collation is used for both.

For example:

-- Assume there's a table with this column declaration:
CREATE TABLE table_a
(
    col_a STRING COLLATE 'und:ci',
    col_b STRING COLLATE '',
    col_c STRING,
    col_d STRING COLLATE 'und:ci'
);

-- This runs. Column 'b' has a collation specification and the
-- column 'c' doesn't.
SELECT STARTS_WITH(col_b_expression, col_c_expression)
FROM table_a;

-- This runs. Column 'a' and 'd' have the same collation specification.
SELECT STARTS_WITH(col_a_expression, col_d_expression)
FROM table_a;

-- This runs. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is considered the default collation
-- because it's assigned to an empty collation specification.
SELECT STARTS_WITH(col_a_expression, col_b_expression)
FROM table_a;

-- This works. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is updated to use the same
-- collation specification as column 'a'.
SELECT STARTS_WITH(col_a_expression, COLLATE(col_b_expression, 'und:ci'))
FROM table_a;

-- This runs. Column 'c' doesn't have a collation specification, so it uses the
-- collation specification of column 'd'.
SELECT STARTS_WITH(col_c_expression, col_d_expression)
FROM table_a;

Collation specification details

A collation specification determines how strings are sorted and compared in collation-supported operations. You can define a collation specification for collation-supported types. These types of collation specifications are available:

Binary collation specification
Unicode collation specification

If a collation specification isn't defined, the default collation specification is used. To learn more, see the next section.

Default collation specification

When a collation specification isn't assigned or is empty, 'binary' collation is used. Binary collation indicates that the operation should return data in Unicode code point order. You can't set binary collation explicitly.

In general, the following behavior occurs when an empty string is included in collation:

If a string has und:ci collation, the string comparison is case-insensitive.
If a string has empty collation, the string comparison is case-sensitive.
If string not assigned collation, the string comparison is case-sensitive.
A column with unassigned collation inherit the table's default collation.
A column with empty collation doesn't inherit the table's default collation.

Binary collation specification

collation_specification:
  'language_tag'

A binary collation specification indicates that the operation should return data in Unicode code point order. The collation specification can be a STRING literal or a query parameter.

The language tag determines how strings are generally sorted and compared. The allowed value for the language_tag is binary.

This is what the binary language tag looks like when used with the ORDER BY clause:

SELECT Place
FROM Locations
ORDER BY Place COLLATE 'binary'

Unicode collation specification

collation_specification:
  'language_tag[:collation_attribute]'

A unicode collation specification indicates that the operation should use the Unicode Collation Algorithm to sort and compare strings. The collation specification can be a STRING literal or a query parameter.

The language tag

The language tag determines how strings are generally sorted and compared. Allowed values for language_tag are:

A standard locale string: This name is usually two or three letters that represent the language, optionally followed by an underscore or dash and two letters that represent the region — for example, en_US. These names are defined by the Common Locale Data Repository (CLDR).
und: A locale string representing the undetermined locale. und is a special language tag defined in the IANA language subtag registry and used to indicate an undetermined locale. This is also known as the root locale and can be considered the default Unicode collation. It defines a reasonable, locale agnostic collation. It differs significantly from binary.
unicode: Identical to binary. It's recommended to migrate unicode to binary.

Additionally, you can append a language tag with an extension. To learn more, see extensions for the language tag.

The collation attribute

In addition to the language tag, the unicode collation specification can have an optional collation_attribute, which enables additional rules for sorting and comparing strings. Allowed values are:

ci: Collation is case-insensitive.
cs: Collation is case-sensitive. By default, collation_attribute is implicitly cs.

If you're using the unicode language tag with a collation attribute, these caveats apply:

unicode:cs is identical to unicode.
unicode:ci is identical to und:ci. It's recommended to migrate unicode:ci to binary.

Collation specification example

This is what the ci collation attribute looks like when used with the und language tag in the COLLATE function:

COLLATE('orange1', 'und:ci')

This is what the ci collation attribute looks like when used with the und language tag in the ORDER BY clause:

SELECT Place
FROM Locations
ORDER BY Place COLLATE 'und:ci'

Extensions

The Unicode Collation Algorithm standard includes some useful locale extensions. In GoogleSQL, a language_tag may be extended by appending -u-[extension] to it and replacing [extension] with your desired Unicode local extension.

This is what the kn-true extension looks like when used with the en-us language tag in the ORDER BY clause:

For example:

SELECT *
FROM UNNEST([
  'a12b',
  'a1b'
]) AS ids
ORDER BY ids COLLATE 'en-us-u-kn-true'

/*-------+
 | ids   |
 +-------+
 | a1b   |
 | a12b  |
 +-------*/

SELECT *
FROM UNNEST([
  'a12b',
  'a1b'
]) AS ids
ORDER BY ids COLLATE 'en-us-u-kn-false'

/*-------+
 | ids   |
 +-------+
 | a12b  |
 | a1b   |
 +-------*/

Here are some commonly used extensions:

Extension	Name	Example
ks-level2	Case-Insensitive	"a1" < "A2"
ks-level1	Accent and Case-Insensitive	"ä1" < "a2" < "A3"
ks-level1-kc-true	Accent Insensitive	"ä1" < "a2"
kn-true	Numeric Ordering	"a1b" < "a12b"

For a complete list and in depth technical details, consult [Unicode Locale Data Markup Language Part 5: Collation] tr35-collation-settings.

Caveats

Differing strings can be considered equal. For instance, ẞ (LATIN CAPITAL LETTER SHARP S) is considered equal to 'SS' in some contexts. The following expressions both evaluate to TRUE:
- COLLATE('ẞ', 'und:ci') > COLLATE('SS', 'und:ci')
- COLLATE('ẞ1', 'und:ci') < COLLATE('SS2', 'und:ci')
This is similar to how case insensitivity works.
In search operations, strings with different lengths could be considered equal. To ensure consistency, collation should be used without search tailoring.
There are a wide range of unicode code points (punctuation, symbols, etc), that are treated as if they aren't there. So strings with and without them are sorted identically. For example, the format control code point U+2060 is ignored when the following strings are sorted:
```
SELECT *
FROM UNNEST([
  COLLATE('oran\u2060ge1', 'und:ci'),
  COLLATE('\u2060orange2', 'und:ci'),
  COLLATE('orange3', 'und:ci')
]) AS fruit
ORDER BY fruit
```

/---------+ | fruit | +---------+ | orange1 | | orange2 | | orange3 | +---------/

+ Ordering _may_ change. The Unicode specification of the `und` collation can
change occasionally, which can affect sorting
order. If you need a stable sort order that's
guaranteed to never change, use `unicode` collation.

## Limitations

Limitations for supported features are captured in the previous
sections, but here are a few general limitations to keep in mind:

+ Table functions can't take table arguments with collated columns.

```googlesql
CREATE TABLE FUNCTION my_dataset.my_tvf(x TABLE<col_str STRING>) AS (
  SELECT col_str FROM x
);

SELECT * FROM my_dataset.my_tvf(
  (SELECT COLLATE('abc', 'und:ci') AS col_str)
);

-- User error:
-- "Collation 'und:ci' on column col_str of argument of TVF call isn't allowed"