Re: SQL - Amount of hits with a LIKE

On 19 Jun 2002 17:44:06 -0700, aishaf_at_datacom.co.nz (AishaF) wrote:

>Hi all,
>I am writting a query that searches through a table using a couple of
>"likes".
>I want something like the following
>---------
>SELECT
> *
>FROM
> Table
>WHERE
> Table.col1 like in ('%prefix%', '%suffix%')
>---------
>
>But you can't have a "like" and an "In" together. I'm guessing I can
>use OR here, but since there might be N amount of words OR'd together,
>it is going to be a little tricky.
>
>My main problem is however, how do I order them, so that words that
>are matched more than once are higher up.
>
>Eg. Like '%ex%' or Like '%le%'
>
>-----
>ExtraSimple
>Extra
>Simple
>------
>
>I would like ExtraSimple to be placed first, since it was matched with
>both '%ex%' and '%le%'
>
>Any ideas?

Well, if you could get your search terms into a table "Terms", then you could do something like

SELECT "Table".Fields, Count(*) As hits
FROM "Table" INNER JOIN terms
ON "Table".coll LIKE '%' || Terms.term || '%' ORDER BY hits DESC

(if your database doesn't like LIKE in a join clause, you can do without it)

You could of course also do something like this:

SELECT "Table".*,
(
 CASE WHEN "Table".coll LIKE '%ex%' THEN 1 ELSE 0 END +
 CASE WHEN "Table".coll LIKE '%le%' THEN 1 ELSE 0 END ) AS hits
FROM "Table"
WHERE hits > 0
ORDER BY hits DESC

-- 
Nis Jorgensen
Amsterdam

Please include only relevant quotes, and reply below the quoted text. Thanks