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 count(*)
from (
      select substr('K_u_n_a_l',level,1) str
      from dual
      connect by level <=length('K_u_n_a_l')
     )
where str  ='_';

Answer 2

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).

Answer 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

Answer 4

REGEXP_COUNT should do the trick:

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

Answer 5

here is a solution that will function for both characters and substrings:

select (length('a') - nvl(length(replace('a','b')),0)) / length('b')
  from dual

where a is the string in which you search the occurrence of b

have a nice day!

Answer 6

I thought of

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