Counting the number of occurrences of a character in Oracle SQL [closed]

Question

How can I count the number of times that a particular character occurs in a column in Oracle? For example, if I have a table FOO that has data like a,ABC,def and 2,3,4,5, I want to count the number of times that a comma appears in the data.

CREATE TABLE foo (
  str varchar2(30)
);

INSERT INTO foo VALUES( 'a,ABC,def' );
INSERT INTO foo VALUES( '2,3,4,5' );
commit;

The output that I want is

str         cnt
a,ABC,def   2
2,3,4,5     3

Answer 1

One of the usual tricks for this is to use a combination of length and replace:

select (length(your_col) - length(replace(your_col, ','))) from your_table;

replace without a third argument will simply remove the character.