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 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 barfooindex ON bar(foo) ORDER BY upper(foo);
at this point, i guess i'm just wanting to avoid the extra run-time
overhead of running the UPPER() function on every foo returned from a
select. i may just add an extra column to the table that's the
upper-case version of title and index that. same net result, but it
would add, of course, to the size of the db.
like:
foo UPPERFOO
Test TEST
any ideas?
- chase
On July 30, 2007, 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 either. It
searches the text case-insensitively, but it still outputs it in the
"wrong" order. Keep in mind that I'm aware that the former is
NUMERICALLY/TECHNICALLY in order, but I need it in "human-understood
alphabetical order" (we'll call it).
We ultimately will be creating an index for this column anyway, so
let's just jump ahead and talk about creating an INDEX which would
spit out:
Da
DC
De
Do
If, however, it has nothing to do with the index and instead we should
deal with this in the SELECT, that's fine. In that case, tell me what
that SELECT statement would look like.
Thanks.
- Chase
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
