How to count occurrences of characters in a SQL statement
February 22nd, 2007 By Frank Zhou
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.
-----------------------------SQL Solution----------------------------
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 regexp_substr(str ,'[^,]+', 1, LEVEL) str
FROM (SELECT :input as str FROM DUAL)
CONNECT BY PRIOR str = str
AND regexp_substr(str,'[^,]+', 1, LEVEL) IS NOT NULL
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;