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

Counting Substring Instances and Ordering Results in MySQL

In MySQL, you can count the occurrences of a substring within a string field and sort the results based on the frequency of those occurrences using the following query:

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

This expression calculates the count of substrings by dividing the difference between the length of the original string and the length of the string with the substring replaced with an empty string by the length of the substring. The results are then sorted in descending order of the count.

Example:

Consider a table with a column host that contains the following values:

'127.0.0.1'
'honeypot'
'honeypot'
'localhost'
'localhost'

To count the occurrences of the substring 'l' in the host column and order the results by the count, you would use the following query:

SELECT host, (CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, 'l', ''))) / CHAR_LENGTH('l') AS cnt
FROM user
ORDER BY cnt DESC

The results would be:

| host      | cnt    |
|-----------+--------|
| localhost | 2.0000 |
| localhost | 2.0000 |
| honeypot  | 0.0000 |
| honeypot  | 0.0000 |
| 127.0.0.1 | 0.0000 |

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