what will be the best way to calculate total ASCII value of the string of characters in SQL

I was searching for this question on StackOverflow but didn’t get any questions that are answering it. So I posted it here.

I want to calculate the ASCII value of the string which is only consisting of characters in a simple way as possible in SQL.

The string is "DESIGNATION OF EMPLOYEE". I want to calculate the total ASCII value of the string.

I tried the longest approach till now, which seems like this –

SELECT SUM(ASCII('D')+ASCII('E')+ASCII('S')+ASCII('I')+.....) AS "TOTAL ASCII" FROM  DUAL;

NOTE: I want to calculate the total ASCII value of the string which includes combined ASCII values of all the characters.

>Solution :

You can use CONNECT BY to unfold the characters, then SUM the ASCII values.

SELECT str
, SUM(ASCII(REGEXP_SUBSTR(str,'.',1,level))) AS "TOTAL ASCII"
FROM (SELECT 'DESIGNATION OF EMPLOYEE' AS str FROM DUAL)
CONNECT BY REGEXP_SUBSTR(str,'.',1,level) IS NOT NULL
GROUP BY str;

db<>fiddle here