Mysql Count in substrings in the columns of a varchar field

Question

I have the following example table:

ID_USER | FRUTAS
-----------------------------
1       | Laranja;Banana;Maçã  
2       | Abacaxi;Laranja;Uva
3       | Pera;Laranja;Banana;Melão

It would be possible to perform a select Count with order by that brings the following result:

FRUTA       |   COUNT
---------------------------
Abacaxi     |   1
Banana      |   2
Laranja     |   3
Maçã        |   1
Melão       |   1
Pera        |   1
Uva         |   1

Answer 1

This query will return what you search for:

SELECT fruta, SUM(total) as count
FROM(
   SELECT count(*) AS total, REPLACE(REPLACE(REPLACE(x.fruta,'?',''),'.',''),'!','') as fruta
   FROM(
      SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.frutas, ';', n.n), ';', -1) fruta
      FROM tabela t 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
        ORDER BY n
      ) n
      WHERE n.n <= 1 + (LENGTH(t.frutas) - LENGTH(REPLACE(t.frutas, ';', '')))
      ORDER BY fruta
   ) AS x
   GROUP BY x.fruta
) AS y
GROUP BY fruta

See working on Sqlfiddle

Answer 2

I solved as follows, I created a precedent where, I insert the fruits in a temporary table and in the end I make a simple query in this table counting the frutas, the result is expected. consider that the name of my table is frutas and the field name also if you need to change the procedure and run in your database.

DROP PROCEDURE IF EXISTS sp_get_count_frutas;
DELIMITER |
CREATE PROCEDURE sp_get_count_frutas()
BEGIN
DECLARE qtd_posicoes INT;
DECLARE end_cursor INT DEFAULT 0;
DECLARE qtd_aux INT DEFAULT 1;
DECLARE str_frutas VARCHAR(255);
DECLARE cur_frutas CURSOR   FOR SELECT
                                        frutas
                                FROM frutas;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_cursor = 1;

DROP TABLE IF EXISTS tmp_table_frutas;
CREATE TEMPORARY TABLE tmp_table_frutas(fruta VARCHAR(255));
OPEN cur_frutas;
    REPEAT
        FETCH cur_frutas INTO str_frutas;
        IF NOT end_cursor THEN


            SET qtd_posicoes = (SELECT LENGTH(str_frutas) - LENGTH(REPLACE(str_frutas,';',''))) + 1;


            WHILE qtd_posicoes >= qtd_aux DO

                INSERT INTO tmp_table_frutas(fruta) VALUE(REPLACE(SUBSTRING(SUBSTRING_INDEX(str_frutas, ';', qtd_aux),
                                                                                                        LENGTH(SUBSTRING_INDEX(str_frutas, ';', qtd_aux-1)) + 1),
                                                                                                        ';', ''));
                SET qtd_aux = qtd_aux + 1;
            END WHILE;

            SET qtd_aux = 1;

        END IF;
    UNTIL end_cursor END REPEAT;
CLOSE cur_frutas;

SELECT
        COUNT(*) AS total_fruta,
        fruta
FROM tmp_table_frutas
GROUP BY fruta;

END
|
DELIMITER ;

CALL sp_get_count_frutas();