Using SQL to get the number of occurances of a character in a string in Oracle

Posted on December 6, 2011 By

This SQL Snippet can be used to get the count of a single character in a string using Oracle. We have a seperate post for doing the same thing in SQL Server.

Assume you have the string a,b,c,d,e and you want the count of commas.  Just use the SQL below.

Select length('a,b,c,d,e')-length(replace('a,b,c,d,e',',')) from dual;

Running this query returns 4, the number of items in the list.

How does it work?
In short, we are just calculating the difference between the string length, and the length of the string with the commas removed.

The first length function gives the length of the original string which is 9.   The replace function replaces the commas with nothing giving the string abcde.  The second length function gives the length of the ‘replaced’ string, which is 5.  Then we subtract 5 from 9 giving 4; the number of commas in the string.

This does not have to work on a single character.  It will also work on a string of any length.  You just need to change the second parameter of the replace function to the string you wish to count.

 

SQL     , ,