Find the number of occurence of a character in a given string in Oracle

Scenario: Need to find out the number of occurrence of a particular character in the given string.

Example: Find number of "e"s in the given string ("Oracle is an interesting thing to learn.")

Solution:
select length('Oracle is an interesting thing to learn') - length(replace('Oracle is an interesting thing to learn','e',''))
from dual;
4

Explained:
59-55 = 4, Hence four occurrence.

Another one Use Case:
Get number of comma separated values in a given column where the values are 100,123,124,145.

Use above method to find number of occurrence of character , (comma) in the givens string and add one to that gives you the number of comma separated values.


In Oracle 11g we have new function to save our time and shorten our code length.

REGEXP_COUNT

select regexp_count('EXPERIENCE','E') FROM DUAL;

This will give you the result 4.