How to count the number of occurrences of a charac

Question

How can I count number of occurrences of the character - in a varchar2 string?

Example:

select XXX('123-345-566', '-') from dual;
----------------------------------------
2

Answer 1

SELECT {FN LENGTH('123-345-566')} - {FN LENGTH({FN REPLACE('123-345-566', '#', '')})} FROM DUAL

Answer 2

I justed faced very similar problem... BUT RegExp_Count couldn't resolved it. How many times string '16,124,3,3,1,0,' contains ',3,'? As we see 2 times, but RegExp_Count returns just 1. Same thing is with ''bbaaaacc' and when looking in it 'aa' - should be 3 times and RegExp_Count returns just 2.

select REGEXP_COUNT('336,14,3,3,11,0,' , ',3,') from dual;
select REGEXP_COUNT('bbaaaacc' , 'aa') from dual;

I lost some time to research solution on web. Couldn't' find... so i wrote my own function that returns TRUE number of occurance. Hope it will be usefull.

CREATE OR REPLACE FUNCTION EXPRESSION_COUNT( pEXPRESSION VARCHAR2, pPHRASE VARCHAR2 ) RETURN NUMBER AS
  vRET NUMBER := 0;
  vPHRASE_LENGTH NUMBER := 0;
  vCOUNTER NUMBER := 0;
  vEXPRESSION VARCHAR2(4000);
  vTEMP VARCHAR2(4000);
BEGIN
  vEXPRESSION := pEXPRESSION;
  vPHRASE_LENGTH := LENGTH( pPHRASE );
  LOOP
    vCOUNTER := vCOUNTER + 1;
    vTEMP := SUBSTR( vEXPRESSION, 1, vPHRASE_LENGTH);
    IF (vTEMP = pPHRASE) THEN        
        vRET := vRET + 1;
    END IF;
    vEXPRESSION := SUBSTR( vEXPRESSION, 2, LENGTH( vEXPRESSION ) - 1);
  EXIT WHEN ( LENGTH( vEXPRESSION ) = 0 ) OR (vEXPRESSION IS NULL);
  END LOOP;
  RETURN vRET;
END;