Zahar Hilkevich (from the blog – https://sqlpatterns.wordpress.com – cool blog, you should check it out) posted a riddle on Facebook.
The question was:

“For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.”

EMPNO ENAME       SUM_ASCII
----- ---------- ----------
 7788 SCOTT             397
 7876 ADAMS             358
 7566 JONES             383
 7499 ALLEN             364
 7521 WARD              302
 7934 MILLER            453
 7902 FORD              299
 7369 SMITH             389
 7844 TURNER            480
 7698 BLAKE             351
 7782 CLARK             365
 7654 MARTIN            459
 7839 KING              297
 7900 JAMES             368

This is a really cool question and it has several nice solutions – my first solution was to go “hardcore” SQL.
If we want to perform an operation on each of the letters in a word, we will need to break down that column into single characters. For that, we will need to multiply each row by the number of character that column has. For example, if we have SCOTT and we want to calculate the ASCII value for each letter, in SQL we will need to have 5 rows (S, C, O, T, T) and calculate the ASCII to each character separately and then aggregate them together.
So, this should be simple – we can use the automatic numeric generator (using connect by level) to create a view with N number of rows, find what is our longest work and then create a Cartesian join to multiply the words.
Overall, this looks like that:

select employee_id,
    last_name,
    sum (ascii (last_name_char))
from
    (
        select employee_id,
            last_name,
            substr (last_name, i, 1) last_name_char
        from hr.employees,
            (
                select level i
                from dual
                    connect by level <=
                    (
                        select max (length (h.last_name)) from hr.employees h
                    )
            )
        where length (last_name) >= i
    )
group by employee_id,
    last_name

Oracle 12c Solution

But that wasn’t the end of it. After a few minutes, I figured that Zahar didn’t limit the question to only SQL – he just asked for a single query so I took the liberty of using a cool new feature from Oracle 12c: the embedded PL/SQL function using the with clause.
In Oracle 12c, we can use create an anonymous function to be used only in our query scope. This is good when we don’t need the function to be saved into the database, but still have a problem that is much easier to solve using an iterative algorithm.

When using PL/SQL – the original question becomes very easy:

with 
    function sumascii (str in varchar2) return number is 
    x number := 0;
begin
    for i in 1..length (str)
    loop
        x := x + ascii (substr (str, i, 1)) ;
    end loop;
    return x;
end;
select h.EMPLOYEE_ID, h.last_name, sumascii (h.last_name) from hr.employees h

We create a one-time-use function (sumascii) which loops on the in parameter and returns the sum. No need to multiply the rows and the solution is very elegant.

Cool trick!

For Zahar’s original blog post: https://sqlpatterns.wordpress.com/2016/04/11/puzzle-of-the-week-7-find-the-sum-of-ascii-codes-of-employee-names