2

How do I count the appearance of a word in a text-column in MySQL?

Example:

Row-Data:

"This is a test text for testing"

Word:

"test"

Output:

--> 2 Matches

I want to calculate the relevance of a word in datasets. Therefore I need to know how often a certain column contains the search-phrase.

Mihai
  • 26,325
  • 7
  • 66
  • 81
0xDEADBEEF
  • 3,401
  • 8
  • 37
  • 66

1 Answers1

4
select (length(column)-length(replace(column,'test','')))/4 as COUNT 
FROM table

Replace 4 with the number of letters in your word.

SQL Fiddle

Mihai
  • 26,325
  • 7
  • 66
  • 81
  • 1
    A better written SQL would be instead of 4, to write LENGTH('test') which is the same value, but explains better the logic, cheers for the answer – Noam Rathaus Nov 24 '13 at 14:37