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

[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 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
TestTEST



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]
-

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

[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 would spit
> out:
>
> Da
> DC
> De
> Do

You can use the COLLATE clause when creating the index (or the table
column, or with ORDER BY in a query).  See
http://sqlite.org/datatype3.html#collation for available collations.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[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 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
TestTEST



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]
-

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

[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 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]
-

[sqlite] CREATE INDEX that is case insensitive?

[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 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]
-