| Subject | Re: [firebird-support] Case insensitive search on a memo field |
|---|---|
| Author | Ann Harrison |
| Post date | 2014-09-05T15:18:59Z |
On Fri, Sep 5, 2014 at 8:43 AM, Stefan Heymann lists@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
be different).
What you can do is try to use a case insensitive collation like
unicode_ci or unicode_ci_ai, but I don't know if that really speeds
things up.
I use upper() for that and it's surprisingly fast (lowercase shouldn't> I’m using Firebird 2.5.3, and I am looking for text in a emo field, eg.
> I’m tempted to do something like
> Select * from mytable t where lowercase(t.mymemo) like ‘%find me%’
> but since this memo field could be enormous, I’m guessing that wold be horribly inefficient.
be different).
What you can do is try to use a case insensitive collation like
unicode_ci or unicode_ci_ai, but I don't know if that really speeds
things up.
As Set said before, no index is going to help unless the text string
is at the very beginning of the field. CONTAINING is the best bet -
it's case insensitive, but doesn't take wild cards, so if you're looking
for "%Find%me%", you'll need to use LIKE or SIMILAR and upcase
the incoming value. However, you;re going to read every byte of
every instance of that blob (memo) field - well, every byte of every
instance that does not contain your value, and every byte up to the
value if it does exist.
What's needed is a full-text index, which Firebird doesn't offer. Lucene
and others produce full-text indexes that can be used with Firebird (I
think, haven't tried it).
Good luck,
Ann