Mysql cuenta instancias de subcadena, luego ordena por

Tengo un problema en mySQL que dice lo siguiente:

  • Cuente las instancias de una subcadena en un campo de cadena en una base de datos mySQL
  • Ordene los resultados por el número de ocurrencias de esa subcadena (DESC)

Nunca he hecho más que consultas rudimentarias … No puedo encontrar una solución en otro lugar.

SELECT (CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, substr, ''))) / CHAR_LENGTH(substr) AS cnt ... ORDER BY cnt DESC 

Sí, parece hinchado, pero afaik no hay otra solución posible.

 mysql> select (CHAR_LENGTH('asd') - CHAR_LENGTH(REPLACE('asd', 's', ''))) / CHAR_LENGTH('s'); +-----------------------------------------------------------------+ | (CHAR_LENGTH('asd') - CHAR_LENGTH(REPLACE('asd', 's', ''))) / CHAR_LENGTH('s') | +-----------------------------------------------------------------+ | 1.0000 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select host, (CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, 'l', ''))) / CHAR_LENGTH('l') AS cnt from user; +-----------+--------+ | host | cnt | +-----------+--------+ | 127.0.0.1 | 0.0000 | | honeypot | 0.0000 | | honeypot | 0.0000 | | localhost | 2.0000 | | localhost | 2.0000 | +-----------+--------+ 5 rows in set (0.00 sec) 
 DELIMITER // DROP FUNCTION IF EXISTS `subStringCount`// CREATE FUNCTION `subStringCount` (sequence VARCHAR(1000), word VARCHAR(100)) RETURNS INT(4) DETERMINISTIC CONTAINS SQL BEGIN DECLARE counter SMALLINT UNSIGNED DEFAULT 0; DECLARE word_length SMALLINT UNSIGNED; SET word_length = CHAR_LENGTH(word); WHILE (INSTR(sequence,word) != 0) DO SET counter = counter+1; SET sequence = SUBSTR(sequence, INSTR(sequence,word)+word_length); END WHILE; RETURN counter; END // DELIMITER ; 

Que se puede ejecutar llamando:

 SELECT sum(subStringCount(fieldName,'subString')) FROM `table` WHERE 1 

Después de guardar el procedimiento anterior, esto funcionó para obtener un recuento de la frecuencia de cadena en la tabla …

 SELECT sum(subStringCount(fieldName,'subString')) FROM `table` WHERE 1