How to Count and Order Substring Occurrences in MySQL?

Counting Substring Instances and Ordering by Occurrence in MySQL

In MySQL, you can count the occurrences of a specific substring in a string field and subsequently sort the results based on the number of occurrences in a descending order.

Query:

The following query accomplishes this task:

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

Explanation:

• The outer CHAR_LENGTH() function calculates the total length of the string field str.
• The inner REPLACE() function replaces all instances of the substring substr with an empty string. By subtracting the length of the replaced string from the total length, we obtain the number of occurrences of the substring.
• Dividing this difference by the length of substr gives us the total count of substring occurrences.
• The cnt alias allows us to reference the computed value in the ORDER BY clause, which sorts the results in descending order based on the number of substring occurrences.

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