Re: [sqlite] Stringcompare operator with COLLATE NOCASE

2009-01-11 Thread Lukas Haase 
Igor Tandetnik schrieb:
> "Lukas Haase"  wrote in
> message news:gkd3f4$cl...@ger.gmane.org
>> This is not what I want. So I use COLLATE NOCASE:
>>
>> 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;
> 
> SELECT keyword FROM keywords
> WHERE keyword < 'T*' COLLATE NOCASE
> ORDER BY keyword COLLATE NOCASE ASC;
> 
> Or, you could just specify the collation in the CREATE TABLE statement, 
> then it would be used by default both for comparisons and for sorting:
> 
> CREATE TABLE keywords(
> keywordID INTEGER PRIMARY KEY,
> keyword VARCHAR(100) COLLATE NOCASE
> );

Thank you very much! :-)

Luke



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

Re: [sqlite] Stringcompare operator with COLLATE NOCASE

2009-01-11 Thread Igor Tandetnik 
"Lukas Haase"  wrote in
message news:gkd3f4$cl...@ger.gmane.org
> This is not what I want. So I use COLLATE NOCASE:
>
> 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;

SELECT keyword FROM keywords
WHERE keyword < 'T*' COLLATE NOCASE
ORDER BY keyword COLLATE NOCASE ASC;

Or, you could just specify the collation in the CREATE TABLE statement, 
then it would be used by default both for comparisons and for sorting:

CREATE TABLE keywords(
keywordID INTEGER PRIMARY KEY,
keyword VARCHAR(100) COLLATE NOCASE
);

Igor Tandetnik 



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

[sqlite] Stringcompare operator with COLLATE NOCASE

2009-01-11 Thread Lukas Haase 
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