| Updated: April 19, 2023 |
The examples below identify (in comments) the collation sequences used to determine the results of the text comparisons performed in the various SQL statements. Note that a text comparison may not be required and no collation sequence used in the case of NUMERIC, BLOB, or NULL values.
CREATE TABLE t1(
x INTEGER PRIMARY KEY,
a, /* collating sequence BINARY */
b COLLATE BINARY, /* collating sequence BINARY */
c COLLATE RTRIM, /* collating sequence RTRIM */
d COLLATE NOCASE /* collating sequence NOCASE */
);
/* Text comparison a=b is performed using the BINARY sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3
/* Text comparison a=b is performed using the RTRIM sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4
/* Text comparison d=a is performed using the NOCASE sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4
/* Text comparison a=d is performed using the BINARY sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4
/* Text comparison 'abc'=c is performed using the RTRIM sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3
/* Text comparison c='abc' is performed using the RTRIM sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3
/* Grouping is performed using the NOCASE sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4
/* Grouping is performed using the BINARY sequence. 'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2
/* Sorting or column c is performed using the RTRIM sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3
/* Sorting of (c||'') is performed using the BINARY sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1
/* Sorting of column c is performed using the NOCASE sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1