Count Occurrence of Character in a String Using MySQL

15 04 2008

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


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.




26 responses

5 01 2009

Nice stuff.
It really helped in my query.

14 01 2009

super simple – well done!

27 02 2009
Menghitung jumlah karakter « nan tak (kalah) penting

[…] 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. […]

24 06 2009
Michael B. Bop

Thanks, that helped me out a bunch!

8 07 2009

I was looking for something like this, perfect!

22 07 2009

Great post! I’ll subscribe right now wth my feedreader software!

23 10 2009

Wow,,, that helped me…..
Thanks a lot……

9 12 2009

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);

3 03 2010

Thanks a lot ! it helped me a lot

27 03 2010
Tyron Foston

Thanks for the coding, you confirmed the method I was going to use!

17 04 2010

Very inventive, kudos!

28 12 2010

Just what I was looking for, big tnx 🙂

10 03 2011

thanks great snippet indeed!

6 05 2011

Indeed a great work! Thank you very much.

And special thanks for pneumatyka, as I’m going to use it with a large needle =]

6 07 2011


1 12 2011

realy great logic..

6 01 2012

Neat workaround.

17 01 2012
Mr Goose

That was great, thanks

24 01 2012
Andreas Roussos

Incredibly simple and does the job well, thank you very much for this.

17 02 2012

Very good, thanks

5 05 2012

realy usefull thanks!

5 06 2012

Good one!!!! (I love lateral thinking)

9 08 2012

That was awesome! Thanks!

15 08 2012
Reg Scheepers

Thanks helped me too. Great outside the box thinking there!

25 10 2012
Jeffrey Culverhouse

Thank you – nice trick!

18 04 2013

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.


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: