How to make multiselect with sort from MySQL?

Question

MySQL

A task with a selection from muscle, there is a table in the database in which one of the columns has values ​​\u200b\u200blike those on the screen , I need to make a selection by several numbers.
Options like:

SELECT * FROM `table` WHERE cat LIKE '%10%' or cat LIKE '%25%' or cat LIKE '%27%';
SELECT * FROM `table` WHERE FIND_IN_SET('10', cat) or FIND_IN_SET('25', cat) or FIND_IN_SET('27', cat);

do not work quite correctly, because when sampling, the results are not sorted by the number of matches from highest to lowest.
The first option is bullshit in general, especially if you set it to% 1%, then it will search for me and 1 and 16 and 21 IDs, which is not necessary.
Are there any options for what kind of query should be done to display the results sorted by the number of matches from largest to smallest? That is, when outputting the results, first there are lines where three numbers match, then two, followed by one number, respectively. Most Relevant. In my example, three numbers are used in the query, but they can be from 1 to 10, for example.

Answer 1

It's a clear design error. This table does not follow first normal form , which requires that each tuple in the table contain only one value per attribute. That is, in 1 cell there should be only one value.
Perhaps what you are asking can be solved in some sophisticated way, but most likely further problems will appear related to this error. The best solution would be to normalize the tables in your database to 3rd or at least 2nd normal form. Such problems, after that, should not arise at all.