The lack of this function forced me to come up with a quick and dirty solution:
select
:str as "string",
:chr as "character",
length(:str) - length(translate(:str,chr(0)||:chr,chr(0))) as "count"
from dual;
| string | character | count |
|---|---|---|
| AB^CDE^FGHI^JK^^LMNOPQ^RST^UVWX^YZ | ^ | 8 |
Note: this works on the assumption that you are never going to count character zero.
Anyone else with a better/faster idea?
4 comments:
Hi,
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.
Patrick
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!
Flavio
luvly solution. Like it :-)
Madhu
Hi!
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!
Post a Comment