mysql join if string contains similar values

Question

Assuming I have a table that has column Description with below values:
His name was Jacob King
One of the guy was Jacob. He was taller than them

How do I join these two rows (in MySql) since they both contain the word Jacob? There will be more rows with other words too so Jacob is not the only word than can appear in more than one row. What I want is a way of joining rows with similar words appearing in them. I tried using left join with LIKE keyword as shown below but it didn't work since i am just looking for similar word in sentences
select * from (SELECT id,description FROM `statement`) f1
left JOIN (SELECT id,description FROM `statement`) f2
on f1.description like concat('%' ,f2.description,'%')

The above doesn't work, I think because I am looking for a word as opposed to the entire sentence

question from:https://stackoverflow.com/questions/66064732/mysql-join-if-string-contains-similar-values

Answer 1

You can try usinga join

SELECT concat (s1.description , ' ', s2.description)'
FROM `statement`  s1
INNER JOIN `statement` s2  ON s1.description like  ('%Jacob%')  
    AND  s2.description like  ('%Jacob%')  

or using an input

set @my_word= 'jacob';

SELECT concat (s1.description , ' ', s2.description)'
FROM `statement`  s1
INNER JOIN `statement` s2  ON lower(s1.description)  like  concat('%', @my_word, '%')  
AND  lower(s2.description)  like  concat('%', @my_word, '%')