GoogleSQL for Bigtable 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
Collation is useful when you need fine-tuned control over how values are sorted, joined, or grouped in tables.
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.
Functions
The following example functions propagate collation.
| Function | Notes |
|---|---|
ANY_VALUE |
|
ARRAY_AGG |
Collation on input arguments are propagated as collation on the array element. |
ARRAY_FIRST |
|
ARRAY_LAST |
|
ARRAY_OFFSET |
|
ARRAY_OFFSETS |
|
ARRAY_SLICE |
|
ARRAY_TO_STRING |
Collation on array elements are propagated to output. |
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. |
GREATEST |
|
LEAST |
|
LEFT |
|
LOWER |
|
LPAD |
|
MAX |
|
MIN |
|
NORMALIZE |
|
NORMALIZE_AND_CASEFOLD |
|
NULLIFERROR |
|
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. |
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 |
|
IF |
|
IFNULL |
|
NULLIF |
|
STRUCT |
When you construct a STRUCT, collation on input arguments is propagated on the fields in the STRUCT. |
Collation specification details
A collation specification determines how strings are sorted and compared in collation-supported operations. Binary collation is used by default and indicates that the operation should return data in Unicode code point order. You can't set binary collation explicitly.