Count the number of occurrences of a string in a VARCHAR field?
try this:
select TITLE,
(length(DESCRIPTION )-length(replace(DESCRIPTION ,'value','')))/5 as COUNT
FROM <table>
SQL Fiddle Demo
A little bit simpler and more effective variation of @yannis solution:
SELECT
title,
description,
CHAR_LENGTH(description) - CHAR_LENGTH( REPLACE ( description, 'value', '1234') )
AS `count`
FROM <table>
The difference is that I replace the "value" string with a 1-char shorter string ("1234" in this case). This way you don't need to divide and round to get an integer value.
Generalized version (works for every needle string):
SET @needle = 'value';
SELECT
description,
CHAR_LENGTH(description) - CHAR_LENGTH(REPLACE(description, @needle, SPACE(LENGTH(@needle)-1)))
AS `count`
FROM <table>
This should do the trick:
SELECT
title,
description,
ROUND (
(
LENGTH(description)
- LENGTH( REPLACE ( description, "value", "") )
) / LENGTH("value")
) AS count
FROM <table>