How to count number of occurrence of specify char in varchar

Question

Is this possible in (Oracle) SQL?

I have a varchar for example "This is varchar" and I want to count number of "i" (it's 2)...

Robert · Accepted Answer · 2013-07-17T20:20:15.717

5

Try to use REGEXP_COUNT function as below:

select  REGEXP_COUNT( 'This is varchar', 'i' ) from dual

Here you can find more information about REGEXP_COUNT.

edited Jul 17 '13 at 20:20

answered Nov 21 '12 at 12:50

Robert

Caveat: I'm not an Oracle developer. I'm sure this works very well for the example but I would take heed to the `REGEXP` part of function name; simply substituting in `'.'` in place of `'i'` might not do what one wants it to do. – ta.speot.is Nov 21 '12 at 13:05
@ta.speot.is but this `select REGEXP_COUNT( 'This is varchar.', '[.]' ) from dual` will work :) – Robert Nov 21 '12 at 13:06

score 3 · Answer 2 · edited Nov 22 '12 at 05:10

3

You could remove all of the is and check the length difference.

select length('This is varchar')
       - NVL(length(replace('This is varchar', 'i')) , 0)
from dual;

edited Nov 22 '12 at 05:10

Jeffrey Kemp

answered Nov 21 '12 at 12:47

xdazz

1

EDIT: Need to consider nulls: `select length('iii') - length(replace('iii','i')) from dual;` – Jeffrey Kemp Nov 22 '12 at 05:09

score 1 · Answer 3 · answered Nov 21 '12 at 12:47

1

Try this:

LENGTH(varcharString) - LENGTH(REPLACE(varcharString, 'i', ''))

answered Nov 21 '12 at 12:47

Mahmoud Gamal

3 Answers3