Mysql Select By Best Match With Like

I have this query: SELECT `id` FROM `accounts` WHERE AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host) OR AES_DECRYPT(`email`, :salt) LIKE CONCAT('%',:host) I have 2 re

Solution 1:

You can readily order the results by the number of matches:

SELECT `id`
FROM `accounts`
WHERE AES_DECRYPT(`email`, '123') = CONCAT_WS('@', 'test', 'test.com') OR 
      AES_DECRYPT(`email`, '123') LIKE CONCAT('%','test.com')
ORDER BY ( (AES_DECRYPT(`email`, '123') = CONCAT_WS('@', 'test', 'test.com')) +
           (AES_DECRYPT(`email`, '123') LIKE CONCAT('%','test.com'))
         );

This will work for your example.

Solution 2:

To get records in a specific order, use an ORDER BY clause.

SELECT `id` FROM `accounts` 
WHERE AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host)
OR AES_DECRYPT(`email`, :salt) LIKE CONCAT('%',:host)
order by AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host) desc;

Here we are using a MySQL special. A boolean expression that evaluates to TRUE results in 1. A boolean expression that evaluates to FALSE results in 0. In another DBMS you could write this instead:

order by 
 casewhen AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host)
      then1else0end desc;

Solution 3:

This code should suit you:

SELECT `id` FROM `accounts` 
    WHERE AES_DECRYPT(`email`, :salt) = CONCAT_WS('@',:mailbox,:host)
    OR AES_DECRYPT(`email`, :salt) LIKE CONCAT('%',:host)
    ORDER BY LENGTH(`email`) - LENGTH(REPLACE(`email`, :host, '')) - LENGTH(REPLACE(`email`, :mailbox, '')) DESC