How to know how many OR conditions are satisfied?

Question

How can i know that which of my result has satisfied how many number of conditions/

SELECT 
    [TITLE] 
FROM 
    [M_TIPS] 
WHERE 
    [TITLE] LIKE '%VALUE%' OR 
    [TITLE] LIKE '%SQL%'; 

How to know that which of the result has satisfied only 1 OR condition and which of the result has satisfied both conditions. The number of conditions are not static, it can increase.

Can anyone please help ?

Answer 1

Add that to the result:

SELECT 
    [TITLE] 
    case [TITLE] LIKE '%VALUE%' WHEN true then 1 else 0 end as CONTAINS_VALUE
    case [TITLE] LIKE '%SQL%' WHEN true then 1 else 0 end as CONTAINS_SQL
FROM 
    [M_TIPS] 
WHERE 
    [TITLE] LIKE '%VALUE%' OR 
    [TITLE] LIKE '%SQL%'; 

Answer 2

Short circuiting in a WHERE clause *may prevent any match count exceeding 1 if you use OR.

How about storing your match list in a table variable then counting matches;

DECLARE @MATCH TABLE (SCORE INT, TOKEN VARCHAR(16))
INSERT @MATCH 
      SELECT 1, 'VALUE'
UNION SELECT 1, 'SQL'
UNION SELECT 1, 'CAKE'

SELECT 
    [TITLE], SUM(M.SCORE)
FROM 
    [M_TIPS] T INNER JOIN @MATCH M ON T.[TITLE] LIKE '%' + M.TOKEN + '%'
GROUP BY T.[TITLE]

==
sql             1
value           1
xx sql value xx 2

Answer 3

Assuming you're using a programming language to dynamically generate the queries with the conditions generated as required, then I suggest generating the query like so:

SELECT [TITLE], SUM([CONDITION_COUNT]) FROM
(SELECT [TITLE], 1 [CONDITION_COUNT] FROM [M_TIPS] 
    WHERE [TITLE] LIKE ConditionString1 UNION ALL
SELECT [TITLE], 1 [CONDITION_COUNT] FROM [M_TIPS] 
    WHERE [TITLE] LIKE ConditionString2 UNION ALL
...
SELECT [TITLE], 1 [CONDITION_COUNT] FROM [M_TIPS] 
    WHERE [TITLE] LIKE ConditionStringN) SQ
GROUP BY [TITLE]