Don't know if it helps, but you could try

SELECT * FROM TABLE_NAME
WHERE COLUMN_NAME CONTAINING 'foo'
AND F_MATCHCOUNT(COLUMN_NAME, 'foo')>0
ORDER BY F_MATCHCOUNT(COLUMN_NAME, 'foo') DESC;

If you can put some logic like only being interested in words starting
with 'foo' into your search, then you could add another table
INDEXED_TABLE containing the two fields WORD and PK_TABLE_NAME, use
triggers on TABLE_NAME to fill it and link to this one when doing your
query. I guess you could do this even if you do not know whether 'foo'
is in the beginning or middle of words, though the INDEXED_TABLE would
be considerably bigger than the original table if a COLUMN_NAME
contained e.g. 100 characters and you had to store this into 100
records - one record where each position in the field where first in
the string. Though it would be interesting to learn the result of such
an exercise and it would make your F_MATCHCOUNT irrelevant:

SELECT T.*, COUNT(DISTINCT IT.PK) FROM TABLE_NAME T
JOIN INDEXED_TABLE IT ON T.PK = IT.T_PK
WHERE IT.INDEXED_FIELD STARTING 'FOO'
GROUP BY T.*
ORDER BY 2

HTH,
Set

--- In firebird-support@yahoogroups.com, "Matt Bucknall" wrote:
> Hello,
>
> I have written a UDF ( F_MATCHCOUNT(string, pattern) ) that returns
> the number of times a given pattern string occurs in another string.
> Is there a more efficient way I can perform the following query on
> Firebird 1.5, where I only need to call the UDF once?
>
> SELECT * FROM TABLE_NAME WHERE F_MATCHCOUNT(COLUMN_NAME, 'foo')>0
> ORDER BY F_MATCHCOUNT(COLUMN_NAME, 'foo') DESC;
>
> Thanks,
> Matt.