Comma separated string similarity check in MySQL

I have a table with column that contain comma separated ids like :

ids 
----
1,2,3,4
1,4,5
1,5
2
2,6,9

Now I need to sort these ids based on a given string similarity (common elements). For example if the string be 1,5 the result I need is :

1,5 (exactly the same)
1,4,5 (has 1,5 but also has an extra number)
1,2,3,4 (has only 1)
2 (no matter)
2,6,9 (no matter)

My question is that MySQL has a built-in function to reach above result or I have to write a custom procedure? I tried match-against syntax but result was not acceptable.

Note:

In fact these numbers are tag id and I want to find the most similar product.

Thank you in advance

It is not the right approach to store comma-separate IDs, but I solved a similar problem in the past with this function:

It will score each match of the IDs(that worked for me, you might need to add some logic.

DELIMITER //
CREATE FUNCTION similarity_score(input_string VARCHAR(255), tags_string VARCHAR(255)) RETURNS INT
BEGIN
    DECLARE score INT DEFAULT 0;
    DECLARE tag VARCHAR(255);
    DECLARE remainder VARCHAR(255);
    
    SET remainder = input_string;

    WHILE LENGTH(remainder) > 0 DO
        IF LOCATE(',', remainder) > 0 THEN
            SET tag = SUBSTRING(remainder, 1, LOCATE(',', remainder) - 1);
            SET remainder = SUBSTRING(remainder, LOCATE(',', remainder) + 1);
        ELSE
            SET tag = remainder;
            SET remainder = '';
        END IF;

        IF FIND_IN_SET(tag, tags_string) THEN
            SET score = score + 1;
        END IF;
    END WHILE;

    RETURN score;
END //
DELIMITER ;

-- Usage
SET @input_tags = '1,5';

SELECT ids 
FROM your_table
ORDER BY similarity_score(@input_tags, ids) DESC, LENGTH(ids), ids;

To find the count of matching numbers you can do:

SET @compare = '1,5';
WITH cte as (
  SELECT
      r as m,
      a as x, 
      SUBSTRING_INDEX(SUBSTRING_INDEX(ids,',',a),',',-1) as s,
      ids
  FROM (select row_number() over (order by ids) as r, ids from mytable )m
  CROSS JOIN (select 1 as a union all select 2 union all select 3 union all select 4) b
  WHERE b.a <= length(m.ids)-length(replace(m.ids,',',''))+1
)
SELECT 
  ids, COUNT(*) as matches
FROM cte 
WHERE LOCATE(s,@compare)<>0
GROUP BY ids
;

output

see: DBFIDDLE

When putting more time in this you should be able to give 1,5 a higher ranking than 1,4,5.

If the numbers are between 1 and 10 (or some other small range), use the bits in a SMALLINT UNSIGNED to represent the numbers. So, for "1,4,5", use the value

1<<1 | 1<<4 | 1<<5

That is: 2 | 16 | 32 = 50

Then for "similarity", count the number of bits that are the same. The set "1,2,5" is the number 38, so the number of bits in common is

SELECT BIT_COUNT(50 & 38)

is 2 (namely "1,5"). You could deconstruct 50 & 38 - 34 = 2 + 32 to discover "1,5".

I recommend doing the ORing to construct the number in client code. For example, PHP can use

$bits = 0;
foreach (explode("1,4,5") as $b) {
    $bits |= (1<<$b);
}     // results in $bits == 50

With other boolean operations, you could (for example) compute how many bits are on in one number that are not in the other.

I am currently developing a "wordle" game and using a 26-bit INT UNSIGNED to say which letters are in a word. This leads to two simple boolean expressions to ask "are all of these letters in the word" and "are all of those letters not in the word". ord($letter) - ord('A') gives me 0..25 for A..Z.

Furthermore, by using

ORDER BY(BIT_COUNT(col, 38)) DESC
LIMIT 4

the SELECT will pick the "best 4 matches for "1,2,5".