mysql - Get the row that contains most words of a string in a field

Question

In PHP I have a string $str = 'word1 word2 word3'.

Then I have a mysql-db-field called content.

Which MySQL-statement do I need to get the row that has most words of str in its content?

Example:

• Field of Row1: word1 word2 word1 word1 word2
• Field of Row2: word9 word2 word1 word8 word2
• Field of Row3: word1 word2 word1 word1 word3

Would output Row3, as it contains word1, word2 and word3.

• Field of Row1: word1 word2 word1 word1 word2
• Field of Row2: word9 word2 word2 word8 word2
• Field of Row3: word1 word1 word1 word1 word9

Would output Row1, as it contains word1 and word2.

question from:https://stackoverflow.com/questions/65907693/get-the-row-that-contains-most-words-of-a-string-in-a-field

Answer 1

You could (if you have MySQL8.0 or newer) do:

SET @str = 'word1 word2 word3';

DROP  TABLE IF EXISTS content;
CREATE TABLE content(i integer, str VARCHAR(200));
INSERT INTO content VALUES
    (1,'word1 word2 word1 word1 word2'),
    (2,'word9 word2 word1 word8 word2'),
    (3,'word1 word2 word1 word1 word3');

WITH RECURSIVE abc AS (
   SELECT 
      LEFT(@str,instr(@str,' ')-1) as a, 
      MID(CONCAT(@str,' '),instr(@str,' ')+1) as b
   UNION ALL
   SELECT LEFT(b,instr(b,' ')-1), MID(b,instr(b,' ')+1)
   FROM abc
   WHERE rtrim(b)<>'')
SELECT
  x.i,
  content.str,
  sum(c)
FROM (
  SELECT 
    a , 
    content.str, 
    content.i,
    (length(content.str)-length(replace(content.str,a,'')))/length(a) as c
  FROM abc
  CROSS JOIN content) x
INNER JOIN content on content.i=x.i
GROUP BY x.i;

output:

i	str	sum(c)
1	word1 word2 word1 word1 word2	5.0000
2	word9 word2 word1 word8 word2	3.0000
3	word1 word2 word1 word1 word3	5.0000