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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
