Monday, June 04, 2007

Tip's Corner: counting how many times a character is in a string

Believe it or not, among the dozens of Oracle's built-in SQL functions, there is none for counting the occurrences of a specific character in a source string.

The lack of this function forced me to come up with a quick and dirty solution:
:str as "string",
:chr as "character",
length(:str) - length(translate(:str,chr(0)||:chr,chr(0))) as "count"
from dual;


Note: this works on the assumption that you are never going to count character zero.

Anyone else with a better/faster idea?


Patrick Wolf said...


Denes did a posting about quite the same a few weeks ago. See his posting Almost to trivial for a post.

His check also works if the search character has more than one char.


Byte64 said...

Hello Patrick,
i did the home job before posting my version and i found at least one link where someone was using the replace function indeed.
However i preferred using translate because it should be more efficient when the search is limited to just one character.
I am just doing a supposition here because i didn't test my statement against the profiler, it might turn out that the more general replace-count version works equally well, but in my juvenile years, when i was an enthusiast of HP calculators, i wrote a translate function in assembly and it was much more efficient compared to the replace, that i had also wrote in ML, so, if the art of writing software is not an opinion, as long as single character counting is concerned, translate is going to be my favorite.
The replace approach has its own advantages in terms of flexibility and if we generalize the problem by using regexp_replace nested into a replace, we could even count the occurrences of a pattern, like counting how many words starting with "ORA" are contained in a string or how many email addresses are there in a string.

Thanks for getting back Patrick!

Anonymous said...

luvly solution. Like it :-)


stefan said...


Here is a solution for finding how many time a string b is repeated in string a, which can be applied also the characters:

select (length('a') - nvl(length(replace(a,b)),0)) / length(b)
from dual

have a nice day!

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles