Is it possible to count the number of repetitions of a string in a single cell of mysql table without using php?

Question

I have a large table (mysql) which stores paragraphs in one of its columns. I would like to count the number of times a specific string is repeated in this column. (PS : I am not interested in counting the number of rows in which the string is present) For example consider the following three rows in that column

---------------------------------
this is a very very big paragraph
---------------------------------
this is a very big paragraph
---------------------------------
this is a small paragraph
---------------------------------

In this case if I search for ' very ' it must return me '3', if I use the below command it would return me '2'.

select count(*) from table_name where column_name like ' very ';

and I am not interested in it.

Is this possible in mysql? or will I need to fetch each paragraph and use php to count the repetation of the string? I am new to SQL.

Your query works fine because you have only 2 times word "very" in you table. It is 1st and 2nd row. Third row don't contain string "very". — KuKeC, Oct 16 '15 at 07:03
yes but in the first cell the string very is repeated twice. And I need to count the repetition also. Is this possible? — Sandeep Thedarkprince C, Oct 16 '15 at 07:06

score 2 · Accepted Answer · answered Oct 16 '15 at 07:09

2

Use this

 SELECT 
        SUM(ROUND (   
            (
                LENGTH(column_name)
                - LENGTH( REPLACE ( column_name, "very", "") ) 
            ) / LENGTH("very")        
        )) AS count    
    FROM table_name

answered Oct 16 '15 at 07:09

Ninju

2,522
2
15
21

Is it possible to count the number of repetitions of a string in a single cell of mysql table without using php?

1 Answers1