SQL query and display only date

Question

CREATE TABLE `celula` (
  `ID_Celula` int(255) unsigned NOT NULL AUTO_INCREMENT,
  `integrantes` text,
  PRIMARY KEY (`ID_Celula`)
);

INSERT INTO `celula` (`ID_Celula`, `integrantes`) VALUES
(1, '2:2014-08-13,4:2014-08-13,6:2014-08-13,7:2014-08-13'),
(2, '3:2014-08-13,5:2014-08-13,6:2014-08-13');

How do I show only the date of the column members?

online test http://sqlfiddle.com/#!2/1729fd/4

Answer 1

You can do something like this

SELECT YEAR(date) year, MONTH(date) month, COUNT(*) count
  FROM
(
  SELECT SUBSTRING_INDEX(value, ':', 1) id, 
         CAST(SUBSTRING_INDEX(value, ':', -1) AS DATE) date
    FROM
  (
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(integrantes, ',', n), ',', -1) value
      FROM celula CROSS JOIN 
    (
      SELECT a.N + b.N * 10 + 1 n
      FROM 
      (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
     ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ) tally
     WHERE n <= 1 + (LENGTH(integrantes) - LENGTH(REPLACE(integrantes, ',', '')))
  ) q
) o
 GROUP BY YEAR(date), MONTH(date)

Output:

| YEAR | MONTH | COUNT |
|------|-------|-------|
| 2014 |     8 |     7 |

Here is SQLFiddle demo

If you need to split more than 100 values you can easily tweak the inner most SELECT to produce more rows, or you can even simplify things you make it a persistent (tally) table in your database.