Mysql: Count the number of occurrences of a string in a VARCHAR field?

I have a table like this:

TITLE          |   DESCRIPTION
------------------------------------------------
test1          |   value blah blah value
test2          |   value test
test3          |   test test test
test4          |   valuevaluevaluevaluevalue

I am trying to figure out how to return the number of times a string occurs in each of the

 DESCRIPTION's.

So, if I want to count the number of times 'value' appears, the sql statement will return this:

TITLE          |   DESCRIPTION                  |   COUNT
------------------------------------------------------------
test1          |   value blah blah value        |   2
test2          |   value test                   |   1
test3          |   test test test               |   0
test4          |   valuevaluevaluevaluevalue    |   5

Is there any way to do this? I do not want to use php at all, just mysql.

 Answers

This should do the trick:

SELECT 
    title,
    description,    
    ROUND (   
        (
            LENGTH(description)
            - LENGTH( REPLACE ( description, "value", "") ) 
        ) / LENGTH("value")        
    ) AS count    
FROM <table> 

---

In SQL SERVER, this is the answer

Declare @t table(TITLE VARCHAR(100), DESCRIPTION VARCHAR(100))

 INSERT INTO @t SELECT 'test1', 'value blah blah value' 

INSERT INTO @t SELECT 'test2','value test' 

INSERT INTO @t SELECT 'test3','test test test'

 INSERT INTO @t SELECT 'test4','valuevaluevaluevaluevalue' 

SELECT TITLE,DESCRIPTION,

       Count = (LEN(DESCRIPTION) - LEN(REPLACE(DESCRIPTION, 'value', '')))/LEN('value')

 FROM @t

Result

TITLE   DESCRIPTION               Count
test1   value blah blah value        2
test2   value test                   1
test3   test test test               0
test4   valuevaluevaluevaluevalue    5

I don't have MySQL install, but goggled to find the Equivalent of LEN is LENGTH 

while REPLACE is same.

So the equivalent query in MySql should be

SELECT 

TITLE,

DESCRIPTION, 

(LENGTH(DESCRIPTION) - LENGTH(REPLACE(DESCRIPTION, 'value', '')))/LENGTH('value') 

AS Count 

FROM 

<yourTable>

Please let me know if it worked for you in MySql also.

---

SELECT 
id,
jsondata,    
ROUND (   
    (
        LENGTH(jsondata)
        - LENGTH( REPLACE ( jsondata, "sonal", "") ) 
    ) / LENGTH("sonal")        
)
+
ROUND (   
    (
        LENGTH(jsondata)
        - LENGTH( REPLACE ( jsondata, "khunt", "") ) 
    ) / LENGTH("khunt")        
)
AS count1    FROM test ORDER BY count1 DESC LIMIT 0, 2

Thanks Yannis, your solution worked for me and here I'm sharing same solution for 

multiple keywords with order and limit.