SQL Query Counting Instances of a Word in a Record

Question

I want to find out how many times a word occurs in a single row.

For Example: I have table sentences and it has only one column call words which is a string data type. The table has only one row with the value "The mans interest in raising the flag flagged."

I want to get the number of times 'the' occurs which is 2 And if I want to get the number of times 'flag' appears it would be 2

Possible duplicate: http://stackoverflow.com/questions/748276/using-sql-to-determine-word-count-stats-of-a-text-field — Babblo, Nov 28 '13 at 02:15
Possible duplicate of [Using SQL to determine word count stats of a text field](https://stackoverflow.com/questions/748276/using-sql-to-determine-word-count-stats-of-a-text-field) — LWC, May 21 '18 at 19:49

score 1 · Accepted Answer · answered Nov 28 '13 at 02:17

1

There is no internal mysql function counting occurences of a substring in a string, but you can compare length of a string to a string with your word replaced by empty strings, as REPLACE() works for all occurences.

SELECT
 (CHAR_LENGTH(sentence)-CHAR_LENGTH(REPLACE(LOWER(sentence),'the','')))/CHAR_LENGTH('the')
 AS occurences
FROM yourtable;

answered Nov 28 '13 at 02:17

piotrm

12,038
4
31
28

Strangely similar to the one from provided by `Babblo` link. – PM 77-1 Nov 28 '13 at 02:37
How would you replace sentence with the data from the columns? – user1572349 Nov 28 '13 at 02:46
Figured out how to replace sentence with the data from column. Thanks. – user1572349 Nov 28 '13 at 02:51

SQL Query Counting Instances of a Word in a Record

1 Answers1