Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

Example:

select XXX('123-345-566', '-') from dual; ---------------------------------------- 2 
like image 231
Ula Krukar Avatar asked Nov 17 '11 15:11

Ula Krukar


People also ask

How do you count occurrences of a character in a string in Oracle?

The Oracle REGEXP_COUNT function is used to count the number of times that a pattern occurs in a string. It returns an integer indicating the number of occurrences of a pattern. If no match is found, then the function returns 0.

What is Regexp_count?

REGEXP_COUNT complements the functionality of the REGEXP_INSTR function by returning the number of times a pattern occurs in a source string. The function evaluates strings using characters as defined by the input character set. It returns an integer indicating the number of occurrences of pattern .

How do I count the number of occurrences in a column in SQL?

Using COUNT, without GROUP BY clause will return a total count of a number of rows present in the table. Adding GROUP BY, we can COUNT total occurrences for each unique value present in the column.


2 Answers

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

like image 57
Flukey Avatar answered Oct 07 '22 02:10

Flukey


REGEXP_COUNT should do the trick:

select REGEXP_COUNT('123-345-566', '-') from dual; 
like image 20
Borodin Avatar answered Oct 07 '22 03:10

Borodin