2

Working with MySQL and I can't find the right combination of terms for this expression.

Using a PHP user input variable in my statement, I need to find a partial substring match and group by the full match word

Taking example input "#bea" I want to find matches for "#bea" as a substring in full text posts, then group by and count the full word where it matches such as "#beat", "#beauty", "#beast"

With example posts in database

Testing Post #beat #beauty

Another test #beauty #beast

Testing #boom #beast

I want to search for a substring, example "#bea"

and have the output

Match   | Count
#beast  | 2
#beauty | 2
#beat   | 1

Can this be done with MySQL or am I better finding matches and counting full words using a PHP algorithm?

Arran
  • 156
  • 3
  • 15
  • GROUP BY, LIKE, COUNT(), etc. – jarlh Feb 27 '18 at 08:26
  • I can use LIKE and GROUP BY but this returns the full post, I'm looking for a way to only return the word in which the substring was found, not the full text post. – Arran Feb 28 '18 at 00:21

2 Answers2

0

Try this:

SELECT A.word `Match`, COUNT(B.sentence) `Count`
FROM 
(SELECT '#beast' word UNION ALL
SELECT '#beauty' word UNION ALL
SELECT '#beat' word) A LEFT JOIN Sentence B
ON INSTR(B.sentence,A.word)>0
GROUP BY A.word
ORDER BY  `Count` DESC, A.word;

See DEMO on SQL Fiddle and INSTR() function.

cdaiga
  • 4,861
  • 3
  • 22
  • 42
0

You can use below query to find count of each word.

SELECT
sum(INSTR( '#beast' , TABLE.COL) > 0) '#beast',
sum(INSTR( '#beauty' , TABLE.COL) > 0) '#beauty'
FROM
TABLE

Then you can pivot table if needed; MySQL pivot table

Pelin
  • 936
  • 5
  • 12