--- In firebird-support@yahoogroups.com, "phil_hhn" <time_lord@...> wrote:

>
> Hi, I'm using Firebird 1.5.3.
> I'm trying to determine whether there is a way to do a wildcard search
> in a (var)char field where the field may or may not contain multiple
> words. (Ignore case-sensitive issues for now!)
>
> Eg I am searching for "lane" and I only want to find records where a
> word starts with "lane". Therefore I want to field containing
> "checkout lane", "lane three" but not one with "planet" (so therefore
> "%lane" will not discriminate enough for my purposes).
> [I eventually also want to find "lane" if it is preceded by a
> non-alpha character (eg - or /).]
> So currently I have the bit of SQL:
> "... where myfield like 'lane%' or myfield like '% lane%'"
>
> This works, but adding "or myfield like '% lane%'" has degraded
> performance significantly (especially on the MAC), and adding:
> "or myfield like '%-lane%' or myfield like '%/lane%'"
> seems cumbersome and inefficient, and makes the query speed worse.
>
> Can anyone suggest a wildcard way of doing this and/or the most
> efficient way to do this?
>
> I gather in SQL Server (not familiar with it) you can do:
> "... where myfield like 'lane%' or myfield like '%[^a-z]lane%'"
>
> Is there something similar in firebird?
> Is there more support for this in Firebird 2 (which we cannot yet move
> to)?

No.

Your performance problem stems solely from the conditions where your
like can not be internally converted to starting with.

So

where myfield like 'lane%'

is fine because it is the same as

where myfield starting with 'lane%'

which is able to use any index available on myfield

But

myfield like '% lane%'"

can not use an index. Given it is in an 'or' statement, every record
must be investigated anyway.


Adam