CODEWITHSUNDEEP
mysql
Surekha
Surekha

Reputation: 87

count of comma separated values in column mysql

SELECT ifnull(LENGTH(GROUP_CONCAT(varchar SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(varchar SEPARATOR ','), ',', '')) + 1,0) AS total    
FROM tablename    
WHERE date(date and time)=curdate()

varchar this column contain comma separated values with this I am getting count of comma separated values in column exactly but not working for large data.

is there any solution to get count without length problem

Upvotes: 0

Views: 893

Answers (1)

jkavalik
jkavalik

Reputation: 1316

You may be hitting limit of group_concat_max_len - that means that anything longer than (by default) 1024 chars will be truncated so you don't get all results.

Possible solution is counting items per row and summing rows counts.

SELECT SUM(ifnull(LENGTH(varchar) - LENGTH(REPLACE(varchar, ',', '')) + 1,0)) AS total    
FROM tablename    
WHERE date(date and time)=curdate()

That way you don't need to think about that limit at all.

Just raising group_concat_max_len is not really a viable solution because you will only hit it later.

The real solution is to get rid of comma separated lists and introduce another table where each item will be separate row. That way most counts and similar operations are simpler.

Upvotes: 1

Related Questions