How to Count Substring Occurrences and Sort Results in MySQL?

Counting Instances of a Substring and Sorting by Occurrence (MySQL)

Problem:

You want to count the occurrences of a specific substring within a string field in a MySQL database. Additionally, you need to order the results descending based on the number of occurrences.

Solution:

To accomplish this, you can use the following query:

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): This function returns the length of the string str.
• REPLACE(str, substr, ''): This function replaces all occurrences of substr in str with an empty string, effectively removing them.
• CHAR_LENGTH(REPLACE(str, substr, '')): This determines the length of the string with the substring removed.
• (CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, substr, ''))): This calculates the number of occurrences of substr by subtracting the length of the modified string from the original string.
• CHAR_LENGTH(substr): This specifies the length of the substring to count.
• cnt: This is an alias for the calculated number of occurrences.

The ORDER BY cnt DESC clause orders the results from the query in descending order, placing the records with the highest number of substring occurrences at the top.

Example Usage:

Suppose you have a table called strings with a column text containing various strings. To count the occurrences of the substring "the" in the text column and order the results by the number of occurrences:

SELECT (CHAR_LENGTH(text) - CHAR_LENGTH(REPLACE(text, 'the', ''))) / CHAR_LENGTH('the') AS cnt
FROM strings
ORDER BY cnt DESC

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