Re: [sqlite] CREATE INDEX that is case insensitive?

2007-07-30 Thread John Stanton
Lower case and upper case are different, with lower case having the higher vlaue. To get case insensitive sorts do this: CREATE TABLE mytab (a TEXT COLLATE NOCASE); then SELECT a FROM mytab ODRER BY a; will give a case insensitive sorted list. Chase wrote: ok. here's a SELECT that

Re: [sqlite] CREATE INDEX that is case insensitive?

2007-07-30 Thread Trevor Talbot
On 7/30/07, Chase <[EMAIL PROTECTED]> wrote: > Right now, when i do a select in sqlite that is supposed to be in > alphabetical order, i get: > > DC > Da > De > Do > We ultimately will be creating an index for this column anyway, so > let's just jump ahead and talk about creating an INDEX which

Re: [sqlite] CREATE INDEX that is case insensitive?

2007-07-30 Thread Chase
ok. here's a SELECT that works... SELECT foo FROM bar WHERE foo LIKE 'D%' ORDER BY upper(foo); but, how could that upper(foo) part be used with the CREATE INDEX syntax? neither of the following attemps worked (syntax errors): CREATE INDEX barfooindex ON bar upper(foo); or CREATE INDEX

Re: [sqlite] CREATE INDEX that is case insensitive?

2007-07-30 Thread John Stanton
It is in correct order. You might try COLLATE NOCASE to force an uper case only sort. Chase wrote: Right now, when i do a select in sqlite that is supposed to be in alphabetical order, i get: DC Da De Do instead of: Da DC De Do The LIKE operator doesn't seems to be helping me here

[sqlite] CREATE INDEX that is case insensitive?

2007-07-30 Thread Chase
Right now, when i do a select in sqlite that is supposed to be in alphabetical order, i get: DC Da De Do instead of: Da DC De Do The LIKE operator doesn't seems to be helping me here either. It searches the text case-insensitively, but it still outputs it in the "wrong" order. Keep