How to count the number of occurrences of a character in an Oracle varchar value?

Question

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

Example:

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

Solution 1

Here you go:

select length('123-345-566') - length(replace('123-345-566','-',null)) 
from dual;

Technically, if the string you want to check contains only the character you want to count, the above query will return NULL; the following query will give the correct answer in all cases:

select coalesce(length('123-345-566') - length(replace('123-345-566','-',null)), length('123-345-566'), 0) 
from dual;

The final 0 in coalesce catches the case where you're counting in an empty string (i.e. NULL, because length(NULL) = NULL in ORACLE).

Solution 2

REGEXP_COUNT should do the trick:

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

Solution 3

Here's an idea: try replacing everything that is not a dash char with empty string. Then count how many dashes remained.

select length(regexp_replace('123-345-566', '[^-]', '')) from dual

Solution 4

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;

Solution 5

I thought of

 SELECT LENGTH('123-345-566') - LENGTH(REPLACE('123-345-566', '-', '')) FROM DUAL;

View more solutions

Comments

[Rainer Stenzel]

Regarding the performance we interestingly found the REGEXP_COUNT solution to be about 5 times slower (more CPU time consuming) than the LENGTH-LENGTH(REPLACE()) approach. Oracle 11.2.0.4 Linux x86 64-bit

[Flukey]

Only supported in Oracle 11. Nice solution though.

[bpgergo]

+1 it is good to know that there is a REGEXP_COUNT function as well.

[Borodin]

Shame. Didn't notice the OP was on 10g

[collapsar]

What kind of syntax is this ?

[kayess]

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value.

[kevthanewversi]

Very smart this answer ;)

[Faisal]

You should also provide some explanation

[David Faber]

Regular expressions work by traversing the string, not by starting the search over at the begining each time, so REGEXP_COUNT() will always and correctly (from the POV of regular expressions) return 1 for your example as well as similar ones. It finds the first occurrence of ,3,, then starting from the next position in the string, looks for the pattern again and doesn't find it.