Using SQLite

Name

COLLATE — Associate a specific collation to an expression

Syntax

Description

The COLLATE operator associates a specific collation with an expression. The COLLATE operator does not alter the value of the expression, but it does change how equality and ordering are tested in the enclosing expression.

If two text expressions are involved in an equality or order test, the collation is determined with the following rules:

1. If the lefthand (first) expression has an explicit collation, that is used.

2. If the righthand (second) expression has an explicit collation, that is used.

3. If the lefthand (first) expression is a direct reference to a column with a collation, the column collation is used.

4. If the righthand (second) expression is a direct reference to a column with a collation, the column collation is used.

5. The default BINARY collation is used.

For example:

'abc'                == 'ABC'                   => 0 (false)
'abc' COLLATE NOCASE == 'ABC'                   => 1 (true)
'abc'                == 'ABC' COLLATE NOCASE    => 1 (true)
'abc' COLLATE NOCASE == 'ABC' COLLATE BINARY    => 1 (true)
'abc' COLLATE BINARY == 'ABC' COLLATE NOCASE    => 0 (false)

For more information on collations, see Collation Functions.

See Also

CREATE TABLE, sqlite3_create_collation() [C API, Ap G]