CODEWITHSUNDEEP
mysqlregexmysql-workbench
None
None

Reputation: 281

MySQL: How to check if a word appears more than once in a string

I have a table with three column say: name,flag, and value, where name is a primary key of type string, flag contains integer, and value contains a LONGTEXT.

The LONGTEXT contains strings which may contains a word that can appear multiple times.

I know that I can search for specific string using LIKE. But I want to query the rows where a specific word say my-word appear more than once, and I want to know how many times it appeard (2,3, etc.) in each cell?

Is this possible?

Upvotes: 1

Views: 1668

Answers (1)

None
None

Reputation: 281

The query should be like this:

select value, (length(value)-length(replace(value,'my-word','')))/7
from mytable
where value like '%my-word%'

I divide by 7 because the word my-word contains 7 characters. So the number you divide by should be the number of characters of the word you want to count how many times it appeared.

Upvotes: 2

Related Questions