Geekman Geekman - 7 months ago 14
SQL Question

Count the number of occurences 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.

Answer

This should do the trick:

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