Sunday, September 23, 2012

How to count character occurences in a text using PLSQL

Find how many "\" in the given string:

1. E.g. string = '12\34\567\89\kkk   '
select length(string )-length(replace(string ,'\',''))
from dual;

2. with t as (
select string from dual)
select length(regexp_replace( string , '[^\]')) / length('\') cnt
from t;

3. SELECT LENGTH(REGEXP_REPLACE(string ,'[^\]')) FROM DUAL;

*********** Following works only in 11g and higher.. **************

4.select  regexp_count('this@here@there.com', '[@]') from dual; 

5. for 2 or more characters as one
SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i') REGEXP_COUNT
   FROM DUAL;




No comments:

Post a Comment