Re: LIKE case-insentive ?

Jonathon Coombes wrote:

>On Wed, 2005-09-07 at 18:41 -0400, Andrew Jensen wrote:
>  
>
>I know that some databases has the two keywords LIKE and ILIKE,
>one for case-sensitive and the other for case-insensitive matching.
>Does this work at all?
>
>Regards
>Jonathon
>  
>
OK..taking another look at this-

HSQLDB doesn't seem to recognize ILIKE...but, read on..

First Table WORDS with a column WORD of VARCHAR and the following entries
dog
Dóg
DoG
DOg

Run the select statement I put up earlier
SELECT "WORD" FROM "WORDS" "WORDS" where ucase("WORD") like ucase('dog')
and the result result set =
DOG
dog
DoG
DOg

We do not get Dóg

However, HSQLDB has a special datatype of VARCHAR_IGNORECASE and I have 
to admit this is the
first time I have tried using it.

So I created this table
Table IWORDS with a column word of VARCHAR_IGNORECASE and the following 
entries
dOG
DOG
Dog
doG
DÒG

Use this select statement
SELECT "word" FROM "IWORDS" "IWORDS" where "word" like 'Dog'
and the result set =
dOG
DOG
Dog
doG

Just what we would expect. However change the select to this ( why? , 
lets just call it a hunch)
SELECT "word" FROM "IWORDS" "IWORDS" where "word" like 'D*g'
and the result set =
dOG
DOG
Dog
doG
DÒG

hmmm...might be something here

So I went back to the original table and did the same change
SELECT "WORD" FROM "WORDS" "WORDS" where ucase("WORD") like ucase('d*g')
and the result set =
NULL

OK..now it is getting interesting

getting back to our case insensitive table and changing the select to
SELECT "word" FROM "IWORDS" "IWORDS" where "word" like 'D*'
yeilds a result set of
dOG
DOG
Dog
doG
DÒG

Here I decided maybe I should look at the HSQLDB docs again...(novel 
idea, hey) and this is what it says about like

"The LIKE keyword uses '%' to match any (including 0) number of 
characters, and '_' to match exactly one character. To search for '%' or 
'_' itself an escape character must also be specified using the ESCAPE 
clause. For example, if the backslash is the escaping character, '\%' 
and '\_' can be used to find the '%' and '_' characters themselves. For 
example, SELECT .... LIKE '\_%' ESCAPE '\' will find the strings 
beginning with an underscore."

great it doesn't even mention the * character... :-D .

ok..I added the following words to the table
dig
dance
DÃNCE
digger
dagger
dăgger
do

going back and changing the select statement to
SELECT "word" FROM "IWORDS" "IWORDS" where "word" like 'D_g_%r'
result set =
digger
dagger
dăgger

while (note that this is 2 _ characters )
SELECT "word" FROM "IWORDS" "IWORDS" where "word" like 'D__*'
and now we get
dOG
DOG
Dog
doG
DÒG
dig
dance
DÃNCE
digger
dagger
dăgger

Everything except do...

One final check, going back to our case sensitive table WORDS
SELECT "WORD" FROM "WORDS" "WORDS" where ucase("WORD") like ucase('d%g')

Guess what...we got what we wanted in the first place..life is a circle 
they say
DOG
dog
DoG
DOg
DÒG

Anyway this is enough of this ...

HTH somehow

Drew Jensen