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]
