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

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.

Advertisements

Actions

Information

26 responses

5 01 2009
Tanuja

Thanks!!!.
Nice stuff.
It really helped in my query.

14 01 2009
iambrian

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
mat

I was looking for something like this, perfect!

22 07 2009
LnddMiles

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

23 10 2009
Ankit

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

9 12 2009
pneumatyka

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
Porfirio

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
mh

Very inventive, kudos!

28 12 2010
moto

Just what I was looking for, big tnx 🙂

10 03 2011
dave

thanks great snippet indeed!

6 05 2011
wKad

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
06ctt

thank!

1 12 2011
sumesh

realy great logic..

6 01 2012
Remi

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
supergiovine

Very good, thanks

5 05 2012
markus

realy usefull thanks!

5 06 2012
betuto92

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

9 08 2012
messenjah00

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
riscadoelectric

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.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s




%d bloggers like this: