Find number of matches for substring inside a GROUP

I have a query with a Group_Concat that returns a set of comma separated numbers. I want to count how many instances of 0,0 are in the group_concat.

e.g. 1,2,0,1 should return 0

1,2,0,0,3,0 should return 1

1,2,0,0,0,6 should return 2

1,2,0,0,0,6,0,0,1,0 should return 3

How would I go about doing this?

CodePudding user response：

WITH RECURSIVE
cte AS ( SELECT id, val, LOCATE('0,0', val) pos
         FROM test
         UNION ALL
         SELECT cte.id, cte.val, LOCATE('0,0', cte.val, 1   cte.pos)
         FROM cte
         WHERE cte.pos > 0
         )
SELECT id, val, SUM(pos > 0) cnt
FROM cte
GROUP BY 1,2;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d0d16594e8503ac36d01ee23c6304b6d