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

Counting Substring Instances in MySQL and Ordering by Count

In MySQL, finding the number of occurrences of a substring within a string column and ordering the results based on that count can be achieved through a combination of string manipulation and aggregate functions. To address this challenge, let's delve into the solution:

The query below leverages the CHAR_LENGTH() and REPLACE() functions to calculate the number of substring occurrences:

SELECT (CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, substr, ''))) / CHAR_LENGTH(substr) AS cnt

• CHAR_LENGTH(str) determines the length of the entire string.
• CHAR_LENGTH(REPLACE(str, substr, '')) calculates the length of the string after replacing all instances of the substring with an empty string, effectively resulting in the length of the string without the substring.
• By subtracting the latter from the former and dividing by the length of the substring, we obtain the count of substring occurrences.

To further sort the results in descending order of substring counts, add the following:

ORDER BY cnt DESC

This will present the data sorted by the number of substring occurrences, with the highest count appearing first.

An example usage of this query:

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

This query retrieves all unique hosts from the user table, counts the occurrences of the substring 'l' within each host, and orders the results by the count, displaying the host with the most occurrences of 'l' at the top.

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