• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

No. of occurences in oracle


I have a string 'aabbbccc''

Now, I wanted to find out how many 'a' are there in this string.. Can someone guide me?

Quick response is appreciated!
0
d27m11y
Asked:
d27m11y
3 Solutions
 
slightwv (䄆 Netminder) Commented:
The quick and dirty something like:
select length('aabbbccc')-length(replace('aabbbccc','a')) from dual;

If we knew a little more about the actual requirements we might be able to clean it up some.
0
 
mrjoltcolaCommented:
Another using regex, trims all non a characters, then computes length of what is left.

 select length(regexp_replace('aabbbaccc', '[^a]', '')) from dual;

And a case-insensitive approach:

 select length(regexp_replace('aabbbAccc', '[^Aa]', '')) from dual;
0
 
OP_ZaharinCommented:
- if you are on Oracle 11g, you can use REGEXP_COUNT as follows:
SELECT 'aabbbccc', REGEXP_COUNT('aabbbccc','a') counter FROM dual
or
SELECT columnA, REGEXP_COUNT(columnA,'a') counter FROM tblname

- more REGEXP_COUNT option here:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions135.htm
0
 
awking00Commented:
Since the third parameter of regexp_replace defaults to null, you can further simplify mrjoltcola's expression to
select length(regexp_replace('aabbbaccc', '[^a]')) from dual;
0
 
d27m11yAuthor Commented:
Easy to follow!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now