Manikandan K Manikandan K - 1 year ago 81
MySQL Question

Get count of similar values in a comma separated column in MYSQL

Hi I would like to get the count of similar values in a mysql column for example, I have to get count of '10' from product_id('9,10,9,10,9,10') and its result should be like '3' using MySQL. Is there a way to do that

Answer Source

@JaydipJ has the right idea but a safer method is:

SET @str = '9,10,9,10,9,10';
SET @tofind = '10'

SELECT (LENGTH(CONCAT(',', @str, ',') - 
        LENGTH(REPLACE(CONCAT(',', @str, ','),
                       CONCAT(',', @tofind, ','
                            ), ''
       ) / LENGTH(@tofind)

This should return the correct result if the string contains 101 or 101010.

As mentioned in the comments. Storing lists in SQL in a comma-delimited list is a bad idea. Such string manipulation would typically be done in a more appropriate language.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download