[sqlite] Will collate binary supports like query

[dd]

Hi,

  Needs to support case-sensitive for case-insensitive sqlite
database field . To find exact case, COLLATE BINARY,  works very well.



  What is the work around for like queries.  Needs to case-sensitive for
like queries and lessthan, greaterthan operators.

  OR

  Is there anyway to change sqlite column's collating from NOCASE to BINARY?



sqlite> .schema
CREATE TABLE t (Id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT COLLATE
NOCASE);

sqlite> select * from t;
Id|data
1|abcd
2|defg
3|ABCD
4|AbCd
5|aBCD
6|Abcd
7|abcd

sqlite> select * from t where (data = 'abcd' collate binary);
Id|data
1|abcd
7|abcd

 // Is it right way to use collate binary for '<' and '>' operations
sqlite> select * from t where data < 'a' collate binary;
Id|data
3|ABCD
4|AbCd
6|Abcd

sqlite> select * from t where data > 'a' collate binary;
Id|data
1|abcd
2|defg
5|aBCD
7|abcd


sqlite> select * from t where data like 'a%' collate binary; //not working
as expected. any alternative.
Id|data
1|abcd
3|ABCD
4|AbCd
5|aBCD
6|Abcd
7|abcd

Thanks.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users