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.