Re: LIKE case-insentive ?

On Wed, 2005-09-07 at 23:14 -0400, Andrew Jensen wrote:
> 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

Yes, that is because you have specified the character 'o' rather
than the character 'ó' which are two different characters.

> However, HSQLDB has a special datatype of VARCHAR_IGNORECASE and I have 
> to admit this is the
> first time I have tried using it.
> 
8<-8<-8<-8<-8<-8<-8<
> 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 .

Yes, the standard for SQL in any database is to use the '%' and '_'
characters as wildcards.

> ok..I added the following words to the table
> dig
> dance
> DÃNCE
> digger
> dagger
> dăgger
> do
8<-8<-8<-8<-8<-8<-8<
> 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

So using the correct syntax is always a good help ;)
Interesting bit about the case-insensitive field type though.
I will add some of your discoveries to the knowledgebase for
any future user that may have problems.

Regards
Jonathon