Mysql – Count the number of occurrences of a string in a VARCHAR field

MySQLsql

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.

Best Answer

This should do the trick:

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

Related Solutions

Sql – How to find duplicate values in a table in Oracle

Aggregate the column by COUNT, then use a HAVING clause to find values that appear greater than one time.

SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;

Sql 2005 join results

The blackbox way of doing this is with a CROSS APPLY and FOR XML PATH:

declare @t table (id int, link_id int, name varchar(max))
insert into @t select 1, 11, 'test1'
union all select 2, 11, 'test2'
union all select 3, 11, 'test3'
union all select 4, 12, 'test4'

select b.link_id, d.link_names
from (
    select distinct link_id
    from @t a
) b
cross apply (
    select name + ', ' as [text()]
    from @t c
    where b.link_id = c.link_id
    for xml path('')
) d (link_names)

For each row, a CROSS APPLY executes the applied subquery. In this case, the subquery is called twice, for link_id 11 and 12. The subquery then abuses the FOR XML operator to add the strings together.

If you run the query, it will print:

11   test1, test2, test3, 
12   test4,

Best Answer

Related Solutions

Sql – How to find duplicate values in a table in Oracle

Sql 2005 join results

Related Topic