SQL - Amount of hits with a LIKE

From: AishaF <aishaf_at_datacom.co.nz>
Date: 19 Jun 2002 17:44:06 -0700
Message-ID: <471bc519.0206191644.543ef801@posting.google.com>

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?

Thanks,
Aisha Received on Wed Jun 19 2002 - 19:44:06 CDT