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.
In Piscean's Heart 
Thanks!!!.
Nice stuff.
It really helped in my query.
super simple – well done!
[…] Menghitung jumlah karakter By Ivan Lanin Beberapa bahasa pemrograman tidak seberuntung php yang memiliki substr_count yang dapat menghitung jumlah suatu string dalam string yang lain. Sewaktu memerlukan fungsi yang sama di MySQL dan Excel VBA, saya sempat kebingungan bagaimana melakukannya. Mbah Google sekali lagi tidak mengecewakan penggemarnya dan memberikan pranala ini. […]
Thanks, that helped me out a bunch!
I was looking for something like this, perfect!
Great post! I’ll subscribe right now wth my feedreader software!
Wow,,, that helped me…..
Thanks a lot……
Great idea, but it doesn’t work correctly with needle string length greater then 1 character. Which is obvious of course, in this case you need to divide resulting number by needle length.
I used this in my stored function:
RETURN (LENGTH(haystack) – LENGTH(REPLACE(haystack, needle, ”))) / LENGTH(needle);
Thanks a lot ! it helped me a lot
Thanks for the coding, you confirmed the method I was going to use!
Very inventive, kudos!
Just what I was looking for, big tnx 🙂
thanks great snippet indeed!
Indeed a great work! Thank you very much.
And special thanks for pneumatyka, as I’m going to use it with a large needle =]
thank!
realy great logic..
Neat workaround.
That was great, thanks
Incredibly simple and does the job well, thank you very much for this.
Very good, thanks
realy usefull thanks!
Good one!!!! (I love lateral thinking)
That was awesome! Thanks!
Thanks helped me too. Great outside the box thinking there!
Thank you – nice trick!
It will work with any word, even “needle” if you use a replacement string that as the lenght of the string you’re trying to find minus 1.
Ex:
RETURN (LENGTH(haystack) – LENGTH(REPLACE(haystack, needle, ‘ ‘))) / LENGTH(needle);
will work, needle has a length of 6, so just use an empty string with a length of 5 in the replace method.