Re: LIKE case-insentive ?

Hello Andrew and Jonathon,

Thank you for your work.
I have just carried out these tests on my own tables, it's ok :-)

Thanks again.

Henri.

Le Jeudi 8 Septembre 2005 05:41, Jonathon Coombes a écrit :
> 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
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]