How to show data with have most word in search

Question

How can I show data with the most words in the search? For example, I have a table like below:

No	Word
1	b c e f g h j k l
2	a b c d e f g h i j k l
3	a b d f g h i k l
4	a c d e f h i k l
5	a b c d e f g h i j

If I want to show data where "Word" have "a, b, c, d, e" it will show in order rec no 2,5,4,3,1. I tried using this query:

SELECT * FROM table
WHERE Word LIKE '%a%'
OR Word LIKE '%b%'
OR Word LIKE '%c%'
OR Word LIKE '%d%'
OR Word LIKE '%e%'

It works, but it cannot order by most words it has. It only shows the result with order 1,2,3,4,5.

Answer 1

One way

SELECT *
FROM   your_table
       CROSS APPLY (SELECT IIF(Word LIKE '%a%', 1, 0)
                           + IIF(Word LIKE '%b%', 1, 0)
                           + IIF(Word LIKE '%c%', 1, 0)
                           + IIF(Word LIKE '%d%', 1, 0)
                           + IIF(Word LIKE '%e%', 1, 0)) ca(match_count)
WHERE  match_count > 0
ORDER  BY match_count DESC, 
            LEN(Word) ASC /*If two strings have the same number of matches prioritise the shorter one*/

If the table is large performance will be bad as it requires a full scan - but leading wildcards necessitate it anyway.

You can look at full text search to get better performance for this type of thing - example.

Answer 2

You can wrap the existing query as a subquery and then order the results based on the match_count in descending order.

In this query your match_count is same for

rec no 3 and 1

So it will show in order rec no 2,5,4,1,3 using below query.

    SELECT *
FROM (
    SELECT *,
           (LEN(Word) - LEN(REPLACE(Word, 'a', ''))) +
           (LEN(Word) - LEN(REPLACE(Word, 'b', ''))) +
           (LEN(Word) - LEN(REPLACE(Word, 'c', ''))) +
           (LEN(Word) - LEN(REPLACE(Word, 'd', ''))) +
           (LEN(Word) - LEN(REPLACE(Word, 'e', ''))) AS match_count
    FROM your_table_name
    WHERE Word LIKE '%a%'
       OR Word LIKE '%b%'
       OR Word LIKE '%c%'
       OR Word LIKE '%d%'
       OR Word LIKE '%e%'
) AS subquery
ORDER BY match_count DESC;

Note: If there are two strings with the same number of matches, give preference to the shorter one.