Search code examples
sqlmysqlsearchfunctiontext

SQL function to get count of how many times string appears in column?

Is there a function for MySQL that will count the number of times a string occurs in another string or column? Basically I want:

SELECT
    SUB_COUNT('my word', `my_column`) AS `match_count`
FROM `table`

Thanks!

EDIT: I need to know how many times the string appears in a column for each row in a SELECT.

Solution

  • An obvious but unscalable way is like this

    (LENGTH(`my_column`) - LENGTH(REPLACE(`my_column`, 'my word', '')))/LENGTH('my word')

    Have you investigated Full Text search in MySQL?