CODEWITHSUNDEEP
mysqlsql-order-bysql-like
Omid
Omid

Reputation: 4705

Order mysql result set by number of matches in like clause

The scenario is that i have implemented a search query using like operator:

.. WHERE caption LIKE 'hello%' OR text LIKE '%hello'
      OR caption LIKE 'jame%' OR text LIKE 'jame%'

and the table is something similar to:

id | caption | text
---------------------
1  | Hi      | Hi Jame
2  | Hello   | Hello firend
3  | Jame    | Hello jame

so i expect that result set to be order like this:

id | caption | text
---------------------
3  | Jame    | Hello jame
1  | Hi      | Hi Jame
2  | Hello   | Hello firend

because 3rd row has more matches to WHERE and LIKEclauses.

Is there a way to do that ?

Upvotes: 0

Views: 398

Answers (2)

echo_Me
echo_Me

Reputation: 37233

try this

     WHERE caption LIKE 'hello%' OR text LIKE '%hello'
  OR caption LIKE 'jame' OR text LIKE 'jame'
  ORDER BY caption DESC

or easier like that

     WHERE caption LIKE 'hello' 
  OR caption LIKE 'jame'
  ORDER BY caption DESC

Upvotes: 0

cetver
cetver

Reputation: 11829

SELECT            *
FROM     ( SELECT *,
                 CASE
                         WHEN caption LIKE 'hello%' OR text LIKE '%hello'
                         THEN 1
                         WHEN caption LIKE 'jame%' OR text LIKE 'jame%'
                         THEN 2                         
                         ELSE 0
                 END AS weight
         FROM    your_table
         )
         q
WHERE    q.weight > 0
ORDER BY q.weight

Upvotes: 1

Related Questions