Hello,
I have a table containting keywords:
CREATE TABLE keywords(
keywordID INTEGER PRIMARY KEY,
keyword VARCHAR(100)
);
INSERT INTO keywords VALUES(1,'Apple');
INSERT INTO keywords VALUES(2,'apple');
INSERT INTO keywords VALUES(3,'Angle');
INSERT INTO keywords VALUES(4,'Tree');
INSERT INTO keywords VALUES(5,'tee');
Normally, they would not be case sensitive, i.e. they would sort:
Angle
Apple
Tree
apple
tee
This is not what I want. So I use COLLATE NOCASE:
CREATE INDEX keyword ON keywords(keyword COLLATE NOCASE ASC);
SELECT keyword FROM keywords ORDER BY keyword COLLATE NOCASE ASC;
in order to obtain:
Angle
Apple
apple
tee
Tree
This is the result I want. However, now I need a WHERE clause to work
exactly the same. So I tried:
SELECT keyword FROM keywords WHERE keyword < 'T*' ORDER BY keyword
COLLATE NOCASE ASC;
Angle
Apple
But the output should be:
Angle
Apple
apple
tee
(i.e. exactly as above but just all strings smaller). It seems to me
that the string-compare operator (<) ignores the collation from my ORDER
BY clause.
Is there any way to obtain a valid result?
Thank you,
Luke
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
