Count the number of occurrences of a string in a VARCHAR field?
MysqlSqlMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
This should do the trick:
SELECT
title,
description,
ROUND (
(
LENGTH(description)
- LENGTH( REPLACE ( description, "value", "") )
) / LENGTH("value")
) AS count
FROM <table>
Solution 2 - Mysql
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>
Solution 3 - Mysql
try this:
select TITLE,
(length(DESCRIPTION )-length(replace(DESCRIPTION ,'value','')))/5 as COUNT
FROM <table>
Solution 4 - Mysql
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][1] while [REPLACE][2] 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. [1]: http://lists.mysql.com/mysql/183791 [2]: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_replace
Solution 5 - Mysql
Here is a function that will do that.
CREATE FUNCTION count_str(haystack TEXT, needle VARCHAR(32))
RETURNS INTEGER DETERMINISTIC
BEGIN
RETURN ROUND((CHAR_LENGTH(haystack) - CHAR_LENGTH(REPLACE(haystack, needle, ""))) / CHAR_LENGTH(needle));
END;
Solution 6 - Mysql
This is the mysql function using the space technique (tested with mysql 5.0 + 5.5):
CREATE FUNCTION count_str( haystack TEXT, needle VARCHAR(32))
RETURNS INTEGER DETERMINISTIC
RETURN LENGTH(haystack) - LENGTH( REPLACE ( haystack, needle, space(char_length(needle)-1)) );
Solution 7 - Mysql
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.
Solution 8 - Mysql
In most cases, these functions are LENGTH and REPLACE, respectively (SQL Server users will use the built-in function LEN rather than LENGTH):
Example, count num of comma in the string "10,CLARK,MANAGER"
select (length('10,CLARK,MANAGER')-
length(replace('10,CLARK,MANAGER',',','')))/length(',')
as cnt from t1