'Ordering Sql result based on number of token matches from RLIKE

I am trying to implement a simple search query, where I am splitting the search text into tokens and then returning all results that contain any of the tokens, I am using RLIKE 'token1|token2|token3|...', this is working correctly and returning all the result, but now I would want to Order the result by the numbers of tokens from the RLIKE that matches, is that anyway possible anyway? thanks in advance.

SELECT p.* FROM product p 
WHERE p.title RLIKE 'token1|token2|token3';
javamysqlsql


Solution 1:[1]

You can use the operator LIKE for each of the tokens in the ORDER BY clause:

ORDER BY (p.title LIKE '%token1%') +
         (p.title LIKE '%token2%') +
         (p.title LIKE '%token3%') DESC

Each of the boolean expressions p.title LIKE '%tokenX%' evaluates to 1 for true or 0 for false.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 forpas

Related Questions