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

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


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

This question is related to sql oracle count oracle10g

The answer is

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

Similar questions with sql tag:

Similar questions with oracle tag:

Similar questions with count tag:

Similar questions with oracle10g tag: