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
  • 24,788
  • 7
  • 64
  • 78
0xDEADBEEF
  • 3,379
  • 8
  • 35
  • 63

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
  • 24,788
  • 7
  • 64
  • 78
  • 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