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

sql oracle count oracle10g
Ula Krukar picture Ula Krukar · Nov 17, 2011 · Viewed 151.2k times · Source

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

Example:

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

Answer

Flukey picture Flukey · Nov 17, 2011

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

Related questions

Select count(*) from multiple tables

How can I select count(*) from two different tables (call them tab1 and tab2) having as result: Count_1 Count_2 123 456 I've tried this: select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2 But all I have …

Read More
Counting number of joined rows in left join

I'm trying to write an aggregate query in SQL which returns the count of all records joined to a given record in a table; If no records were joined to the given record, then the result for that record should …

Read More
SQL - CASE WHEN count different values

I need to show how many different values every 'id' has. It should look like this: id | component_a | component_b | component_c -------------------------------------------------- KLS11 | none | one | none KLS12 | one | one | none KLS13 | several | one | none KLS14 | one | one | one …

Read More