Count Occurrence of Character in a String Using MySQL « In Piscean’s Heart
Since there is no inbuilt MySQL function to count the occurrence of a character in string, we can do it by using these steps:
1. Counting the number of characters in the original string
2. Temporarily ‘deleting’ the character you want to count and count the string again
3. Subtract the first count from the second to get the number of occurrences
Example:
SELECT LENGTH('foobarfoobarfoobar') - LENGTH(REPLACE('foobarfoobarfoobar', 'b', '')) AS `occurrences`
--> Result: 3
In this example ‘b’ is the string you want to count the number of occurrences.
