SQL Server - SELECT multiple LIKE clauses and return how many columns match

Asked By Brad Brening

27-Jul-07 02:43 PM

Thanks to Hugo Kornelis, (blog: http://sqlblog.com/blogs/hugo_kornelis)
I've made major progress toward creating a query that will take
multiple LIKE clauses and return the records that match along with a
custom column describing how many matches those columns contain.

For example, I have a table with many threaded messages.  Given one or
more search words, I'd like to be able to dynamically build a SQL
query and return the "Relevance" (how many matches were found), the
Thread_ID, and the actual message that matched one or more terms.

In order to do this, Hugo offered the excellent query below:
-------------------------------------------------
SELECT
CASE
WHEN ((Message LIKE '%term1%') AND (Message LIKE '%term2%') THEN 100
WHEN ((Message LIKE '%term1%') OR (Message LIKE '%term2%') THEN 50
END
AS Relevance, Thread_ID, Message FROM tblThread
WHERE (Message LIKE '%term1%') OR (Message LIKE '%term2%') ORDER BY
Relevance DESC
-------------------------------------------------


Since I'm building this query dynamically, I can extend it for even
more terms:
-------------------------------------------------
SELECT
CASE
WHEN ((Message LIKE '%term1%') AND (Message LIKE '%term2%') AND
(Message LIKE '%term3%') AND (Message LIKE '%term4%')) THEN 100
WHEN ((Message LIKE '%term1%') AND (Message LIKE '%term2%') AND
(Message LIKE '%term3%') OR (Message LIKE '%term4%')) THEN 75
WHEN ((Message LIKE '%term1%') AND (Message LIKE '%term2%') OR
(Message LIKE '%term3%') OR (Message LIKE '%term4%'))THEN 50
WHEN ((Message LIKE '%term1%') OR (Message LIKE '%term2%') OR
(Message LIKE '%term3%') OR (Message LIKE '%term4%'))THEN 25
END
AS Relevance, Thread_ID, Message FROM tblThread
WHERE (Message LIKE '%term1%') OR (Message LIKE '%term2%') OR (Message
LIKE '%term3%') OR (Message LIKE '%term4%') ORDER BY Relevance DESC
-------------------------------------------------

This is great in that it does what I want it to do - return the
in the Message column.

However, there's a logical problem here.  (I should note that the
logical flaw is in no way attributed to Hugo's answer - his solution
did exacly what I initially asked).  Using the example above, what if
term1 and term4 match, but term2 and term3 do not?  This logic above
would return 25 - even though it SHOULD be 50.

So, I've been researching IF-THEN, but I'm getting nowhere quick!  I
tried something similar to this, but it fails:

SELECT
(
DECLARE @cnt INT
SET @cnt = 0
IF (Message LIKE '%term1%') @cnt = @cnt + 1
IF (Message LIKE '%term2%') @cnt = @cnt + 1
IF (Message LIKE '%term3%') @cnt = @cnt + 1
RETURN @cnt
)
AS Relevance, Thread_ID, Message FROM tblThread
WHERE (Message LIKE '%term1%') OR (Message LIKE '%term2%') OR (Message
LIKE '%term3%') ORDER BY Relevance DESC

Am I anywhere near the right track?

Thanks in advance;

Brad Brening

27-Jul-07 02:50 PM

You can't use IF THEN in a query.  How about:

SELECT
Relevance = 0 +
CASE WHEN Message LIKE '%term1%' THEN 1 ELSE 0 END
CASE WHEN Message LIKE '%term2%' THEN 1 ELSE 0 END
CASE WHEN Message LIKE '%term3%' THEN 1 ELSE 0 END
CASE WHEN Message LIKE '%term4%' THEN 1 ELSE 0 END
CASE WHEN Message LIKE '%term5%' THEN 1 ELSE 0 END
...

--
Aaron Bertrand
SQL Server MVP

27-Jul-07 03:01 PM

That looks very promising!  I will give that a try.

27-Jul-07 03:15 PM

On Jul 27, 1:50 pm, "Aaron Bertrand [SQL Server MVP]"

Still no luck.  I get an "Incorrect syntax near the keyword CASE"
error.  Here's my query:

SELECT Relevance = 0 +
(
CASE WHEN Message LIKE '%term1%' THEN 1 ELSE 0 END
CASE WHEN Message LIKE '%term2%' THEN 1 ELSE 0 END
CASE WHEN Message LIKE '%term3%' THEN 1 ELSE 0 END
)
, Thread_ID, Message FROM tblThread
WHERE (Message LIKE '%term1%') OR (Message LIKE '%term2%') OR (Message
LIKE '%term3%') ORDER BY Relevance DESC

27-Jul-07 03:26 PM

I forgot the + operators, but why the parens?

SELECT Relevance = 0
+ CASE WHEN Message LIKE '%term1%' THEN 1 ELSE 0 END
+ CASE WHEN Message LIKE '%term2%' THEN 1 ELSE 0 END
+ CASE WHEN Message LIKE '%term3%' THEN 1 ELSE 0 END

27-Jul-07 03:36 PM

That did it!

I had placed those parens trying to avoid the error.

Thanks for you assistance;

Brad Brening