The Oracle (tm) Users' Co-Operative FAQ

How do I count occurrences of characters in a SQL Statement?


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 7th February 2007

Oracle version(s): 10.2

How do I count occurrences of characters in a SQL Statement?

Back to index of questions


The following SQL pattern can be used count occurrences of characters in a SQL Statement?

 

Create table t5 as

select 'AAA BBB CCC' AS ch_str from dual

union all

select 'XXX YYY ZZZ' AS ch_str from dual

union all

select 'OOO PPP QQQQ'AS ch_str from dual;

 

 

SQL> select * from t5;

 

CH_STR

------------

AAA BBB CCC

XXX YYY ZZZ

OOO PPP QQQQ

 

 

SQL> variable input varchar2(28)

SQL> exec :input := 'A,Z,O'

 

PL/SQL procedure successfully completed.

 

SELECT ch , count(*)

FROM

(SELECT SUBSTR(ch_str , LEVEL , 1) as ch

   FROM t5

CONNECT BY PRIOR ch_str = ch_str

AND LEVEL <= LENGTH(ch_str)

AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

)

WHERE ch IN

(SELECT SUBSTR(str,

         INSTR(str, ',', 1, LEVEL  ) + 1,

         INSTR(str, ',', 1, LEVEL+1) -

         INSTR(str, ',', 1, LEVEL) -1 ) str     

   FROM (SELECT ','||:input||','  AS str FROM DUAL)

   CONNECT BY PRIOR STR = STR

   AND INSTR (str, ',', 1, LEVEL+1) > 0

   AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

)

GROUP BY ch

ORDER BY ch

 

CH                               COUNT(*)

------------------------------ ----------

A                                       3

O                                       3

Z                                       3

 

SQL> spool off;


 

Back to top

Back to index of questions