The SQL example is given below to count number of character occurrences in a string in Oracle. You can more customize the SQL as per your requirement.
[code type=”SQL”]SELECT replace(a_string, ‘ ‘, ‘spaces’) Character, COUNT (a_string) Occurrences
FROM ( SELECT SUBSTR (a_string, LEVEL, 1) a_string
FROM (SELECT lower(‘a quick brown fox jumps right over the lazy dog.’)
a_string
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (a_string))
GROUP BY a_string [/code]
The above SQL will count number of occurrences in a string and output would be as below:
CHARACTER OCCURRENCES
—————— ———–
q 1
i 2
k 1
h 2
e 2
z 1
o 4
j 1
s 1
d 1
r 3
g 2
a 2
b 1
p 1
t 2
c 1
u 2
w 1
x 1
y 1
CHARACTER OCCURRENCES
—————— ———–
. 1
spaces 9
n 1
f 1
m 1
v 1
l 1
28 rows selected.
