The examples below identify the collation sequences that would be used to determine the results of text comparisons that may be performed by 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