51

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

Example:

select XXX('123-345-566', '-') from dual;
----------------------------------------
2
Vikrant
  • 4,920
  • 17
  • 48
  • 72
Ula Krukar
  • 12,549
  • 20
  • 51
  • 65
  • 1
    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 – Rainer Stenzel Apr 12 '17 at 07:31

9 Answers9

86

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

GrumpyCrouton
  • 8,486
  • 7
  • 32
  • 71
Flukey
  • 6,445
  • 3
  • 46
  • 71
  • 2
    Very smart this answer ;) – kevthanewversi Jun 15 '16 at 13:19
  • I think it may depend upon MySQL version and/or settings, but using 'null' there can result in an empty result. Better to use an empty string for the replace() call: `length(replace('123-345-566','-',''))` – John Rix Oct 25 '22 at 15:08
78

REGEXP_COUNT should do the trick:

select REGEXP_COUNT('123-345-566', '-') from dual;
Vikrant
  • 4,920
  • 17
  • 48
  • 72
Borodin
  • 126,100
  • 9
  • 70
  • 144
13

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
bpgergo
  • 15,669
  • 5
  • 44
  • 68
5

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;
GrzegorzD
  • 51
  • 1
  • 2
  • 2
    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. – David Faber Mar 28 '18 at 13:37
3

I thought of

 SELECT LENGTH('123-345-566') - LENGTH(REPLACE('123-345-566', '-', '')) FROM DUAL;
Hugh Jones
  • 2,706
  • 19
  • 30
2

You can try this

select count( distinct pos) from
(select instr('123-456-789', '-', level) as pos from dual
  connect by level <=length('123-456-789'))
where nvl(pos, 0) !=0

it counts "properly" olso for how many 'aa' in 'bbaaaacc'

select count( distinct pos) from
(select instr('bbaaaacc', 'aa', level) as pos from dual
  connect by level <=length('bbaaaacc'))
where nvl(pos, 0) !=0
1

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!

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
stefan
  • 19
  • 1
0
SELECT {FN LENGTH('123-345-566')} - {FN LENGTH({FN REPLACE('123-345-566', '#', '')})} FROM DUAL
brimborium
  • 9,362
  • 9
  • 48
  • 76
Abel Pinto
  • 25
  • 1
0
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  ='_';
Aleksej
  • 22,443
  • 5
  • 33
  • 38
Kunal
  • 11
  • 3
  • 1
    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. – kayess Apr 27 '16 at 13:12