How to Count Substring Occurrences and Order by Frequency in MySQL?

Counting Substring Occurrences and Ordering by Frequency in MySQL

Problem:

Your MySQL database contains a string field, and you need to:

1. Count the occurrences of a specific substring in each string.
2. Order the results in descending order of substring occurrence count.

Solution:

To achieve this, you can utilize the following code:

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

Explanation:

• CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, substr, '')) calculates the number of substring occurrences in str.
• CHAR_LENGTH(substr) is the length of the substring.
• cnt is an alias for the calculated occurrence count.
• The ORDER BY cnt DESC clause sorts the results in descending order of substring occurrences.

Example Usage:

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 |
+-----------------------------------------------------------------+

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 |
+-----------+--------+

Note:

This method is reliable but has a potential drawback of potentially being resource-intensive for large datasets.

The above is the detailed content of How to Count Substring Occurrences and Order by Frequency in MySQL?. For more information, please follow other related articles on the PHP Chinese website!