SQL function to get count of how many times string appears in column?
29,631
Solution 1
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?
Solution 2
Depends what you mean by "string occurs" - as a substring or as full value?
Full value case:
SELECT COUNT(*) FROM my_table WHERE my_column = 'my word';
Substring case:
SELECT COUNT(*) FROM my_table WHERE my_column LIKE '%my word%';
Comments
-
Kristopher Ives almost 2 years
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
.