Hi,
the documentation says (on <http://www.sqlite.org/datatype3.html#collation>):
| The expression "x BETWEEN y and z" is logically equivalent to two
| comparisons "x >= y AND x <= z" and works with respect to collating
| functions as if it were two separate comparisons.
However, this is not true when the first operator has an explicit
collation assignment:
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(x);
sqlite> insert into t values('a');
sqlite> insert into t values('A');
sqlite> select * from t where x collate nocase between 'a' and 'b';
a
sqlite> select * from t where x collate nocase >= 'a' and x collate nocase <=
'b';
a
A
It works only on the second and third operators:
sqlite> select * from t where x between 'a' collate nocase and 'b' collate
nocase;
a
A
And adding it to the first operator breaks it again:
sqlite> select * from t where x collate nocase between 'a' collate nocase and
'b' collate nocase;
a
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
