Counting the number of occurrences of a character in Oracle SQL
oracle, regex, sql, string
Solution
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.
Problem
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 ```