This is in the documentation:
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 */
);
/* x a b c d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc');
/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4
The above example is intuitive to me.
But what about this one?
/* Grouping is performed using the BINARY collating sequence. 'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2
First, if d is collated on NOCASE, then, why does the second operation use
BINARY? Is it because of this rule as stated in the documentation: "The
collating sequence set by the COLLATE operator overrides the collating
sequence determined by the COLLATE clause in a table column definition
<http://www.sqlite.org/lang_createtable.html#tablecoldef> ."
Second, was the example of adding concatenation of the ' ' supposed to have
any impact on the result other than showing that the collation is now done
on binary? If I concatenate anything (e.g., 'bigcheeseburger'), it doesn't
make a difference. So, I think the answer is that the concatenation
example is just to demonstrate that binary takes precedence over the
collation setting for the column.
Thanks for advice.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
