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.
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