Mysql count instances of substring, then order by

Question

I have a problem in mySQL that goes as follows:

• Count the instances of a substring in a string field in a mySQL database
• Order the results by the number of occurrences of that substring (DESC)

I have never done anything other than rudimentary queries.. I can't find a solution elsewhere.

Answer 1

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

Yep, looks bloated but afaik there is no any other possible solution.

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)