Press "Enter" to skip to content

Count Number of Characters in a String in Oracle SQL

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.

count number of characters in a string oracle sql

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Be First to Comment

    Leave a Reply

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