|
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? |
|
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;