How to calculate the number of occurrences of a character in varchar in one SQL?

Question

Say there is a variable

v_Source := 'stack#over#flo#w';

How to get the number of occurrences ' #' in it in one SQL query?

+4

user3616462 May 31 '16 at 10:04

2 answers

SELECT REGEXP_COUNT( 'stack#over#flo#w', '#' )
FROM   DUAL

+7

MT0 May 31 '16 at 10:10

Sachu · Accepted Answer · 2016-05-31T10:09:08+0000

select length('stack#over#flo#w') - length(replace('stack#over#flo#w','#',null)) 
from dual;

From oracle 11 you can use REGEXP_COUNT

select REGEXP_COUNT('stack#over#flo#w', '#') from dual;