Hi All,

I have been trying to convert a database over from using no
character set (well cs=NONE I guess) to a character set of
ISO8859_1 and a case-insensitive collation (the loadable one
done by Peter Jacobi).

Using FB v1.5.2 SS on Windows XP SP2

Everything has been working great. "field" = 'aNy CaSe'
comparisons work and so on. But now find that...
"field" STARTING 'aNy'
and "field" LIKE 'aNy%'

both appear to be case sensitive - just like it says in
Helens book :-).

But what I dont understand is why this should be the case. Why
would anyone define a column with case insensitive collations
and then expect STARTING and LIKE to be case sensitive. Its
just perverse! (And especially so since STARTING is supposed
to be able to use an index, which is built from a case
insensitive collation.)

Can anyone tell me if this is intentional behaviour, if whether
it may be a bug in either Firebird or the collation library?


Wondering through the archives I see where it has been suggested
that STARTING can be replaced with BETWEEN. eg:
"field" STARTING 'ABC'
becomes "field" BETWEEN 'ABC' AND 'ABCzzzzz'

Of course the problem with that is that 'z' is not necessarily
the last character in the collation sequence (ie. '[' and '~'
etc), and secondly how many do you add to be safe?

It seems that, if STARTING is supposed to be case sensitive even
with a case insensitive collation, that a better substitute for
STARTING would be:
"field" >= 'ABC' AND "field" < 'ABD'
(ie. replace the last character in the string with the next
character of the collation sequence - assuming you know what
that character is.)

And of course such suggestions dont really help with LIKE, in
which the only case insensitive solution seems to be conversion
of the field and test to upper case.


It is starting to seem that using a case-insensitive collation
may not be the wonderful solution I thought it was going to be.
Any thoughts, comments or ideas?

--
Geoff Worboys
Telesis Computing