CODEWITHSUNDEEP
mysql
Pascal Widmann
Pascal Widmann

Reputation: 15

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

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:

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

Would output Row1, as it contains word1 and word2.

Upvotes: 0

Views: 58

Answers (1)

Luuk
Luuk

Reputation: 14958

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

The WITH RECURSIVE part create a temporary table abc with a many records as words in @str.

After that it is just some work to count the occurrences.

Upvotes: 1

Related Questions