It works very well if I use "COLLATE" as like as follows. sqlite> select * from mytable where data between 'abc/' COLLATE nocase and 'abc0' COLLATE nocase; abc/ ABC/ abc/a ABC/A abc/b ABC/B abc0 ABC0 sqlite> select * from mytable where data between 'abc0' COLLATE nocase and 'abc/' COLLATE nocase; sqlite>
2013/5/22 Yongil Jang <[email protected]> > I've found some unexpected result with "COLLATE" operator. > I don't know what if it is a bug or my misunderstanding. > Please, check below description. > > CREATE TABLE mytable (data); > INSERT INTO "mytable" VALUES('abc'); > INSERT INTO "mytable" VALUES('ABC'); > INSERT INTO "mytable" VALUES('abc/'); > INSERT INTO "mytable" VALUES('ABC/'); > INSERT INTO "mytable" VALUES('abc/a'); > INSERT INTO "mytable" VALUES('ABC/A'); > INSERT INTO "mytable" VALUES('abc/b'); > INSERT INTO "mytable" VALUES('ABC/B'); > INSERT INTO "mytable" VALUES('abc0'); > INSERT INTO "mytable" VALUES('ABC0'); > CREATE INDEX data_idx on mytable(data); > CREATE INDEX data_idx_nocase on mytable(data COLLATE nocase); > > As you can see here I defined two indices that can be used both case and > nocase ordering. > It works very well when I use "COLLATE nocase" with '=' unary operator. > > sqlite> select * from mytable where data = 'abc' COLLATE nocase; > abc > ABC > > But, in case of using BETWEEN operator with "COLLATE nocase", it returns > unexpected result as follows. > > sqlite> select * from mytable where data between 'abc/' and 'ABC0' COLLATE > nocase; > *abc/* > *abc/a* > *abc/b* > *abc0* > sqlite> select * from mytable where data between 'ABC/' and 'abc0' COLLATE > nocase; > *abc* > *abc/* > *ABC/* > *abc/a* > *ABC/A* > *abc/b* > *ABC/B* > *abc0* > *ABC0* > > As you can see below ordered list, > first result set of between query doesn't have upper case data. > And, second result set contains unexpected data as like as 'abc', 'ABC'. > > sqlite> select * from mytable order by data COLLATE nocase; > abc > ABC > abc/ > ABC/ > abc/a > ABC/A > abc/b > ABC/B > abc0 > ABC0 > > Regards, > Yongil. > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
