SQL Get list of all characters in column

I have an Oracle database table (e.g employee) with a lot of addresses in different languages and special characters.

I want to get the list of all distinct characters and have a count of each occurrences.

with data
as
(select level r from dual connect by level <= 100)
select substr( a.address, r, 1 ) as Character , count(*) as Number_occurrence
from employee a, data
where data.r <= length(a.address)
group by substr(a.address,r,1)
order by substr(address) ;

For this example, the length of the address field is 100; that’s why we connect 100 times.

Output:

Character Number_occurrence
! 147
” 356
# 19939
$ 1042
% 47
& 21247
‘ 18708
( 49995
) 48235
* 28379
+ 412
, 8051
– 69373
. 107809
/ 52027
: 590
; 14
< 5
= 18

Script by: Ulpia ITTU Oracle DBA (Montreal)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.