Puzzle of the Week #7: find the sum of ASCII codes of employee names

Puzzle of the Week #7

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

Expected Result:

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

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

13 thoughts on “Puzzle of the Week #7: find the sum of ASCII codes of employee names

  1. Zohar Elkayam April 11, 2016 / 9:42 pm

    Hey,

    Here is a general solution – in SQL only :

    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 = i
    )
    group by employee_id, last_name

    • Zohar Elkayam April 11, 2016 / 9:44 pm

      Pretty display:

      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
      
    • Zahar Hilkevich April 11, 2016 / 10:24 pm

      Please re-test your code (first query) – preferably in scott schema. Something is not right, though the idea is very close.
      The 2nd query is correct.

      • Zohar Elkayam April 12, 2016 / 12:27 am

        both queries (function and pure sql) return the same resultset for hr.employees and for scott.hr (first query and second query in this thread are the same – just with the “code” shortcode)

      • Zohar Elkayam April 12, 2016 / 12:31 am

        ah, reviewed the first query again – you’re right – it was malformed by wordpress – some of the code is missing.

        it’s because of the > and < in lines 13 and 18…

        lol

  2. Zohar Elkayam April 11, 2016 / 9:43 pm

    Here is a much simpler solution, valid in 12c and after (a new cool feature):

    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
    
    • Zahar Hilkevich April 11, 2016 / 10:21 pm

      This one is correct, though essentially it is function based. Anyway, this one is accepted.

  3. sunitha April 11, 2016 / 10:49 pm

    select empno,ename, sum(regexp_substr(nm, ‘\d+’, 1, occ)) as sum_ascii
    from(
    select empno, ename,
    regexp_replace(dump(ename), ‘.*: (\d.*)$’, ‘\1’) nm from scott.emp),
    (select level occ from dual connect by level < 2000)
    group by empno,ename order by 3;

    • Zahar Hilkevich April 11, 2016 / 11:20 pm

      Other than “connect by level < 2000", it looks good.

  4. Ashwini Kumar April 12, 2016 / 1:48 am

    SELECT ename,
    SUM(ascii_val)
    FROM
    (SELECT ename,ascii(SUBSTR (ename, rn, 1)) ascii_val
    FROM emp,
    (SELECT ROWNUM rn
    FROM DUAL
    CONNECT BY LEVEL <=
    (SELECT MAX(LENGTH(ename)) FROM emp)
    ) )
    WHERE ascii_val IS NOT NULL
    GROUP BY ename;

  5. Ashwini Kumar April 12, 2016 / 2:02 am

    select empno,ename, sum(regexp_substr(nm, ‘\d+’, 1, occ)) as sum_ascii
    from(
    select empno, ename,
    substr(dump(ename),instr(dump(ename),’: ‘)+2) nm from emp),
    (select level occ from dual connect by level < (SELECT MAX(LENGTH(ename)) FROM emp))
    group by empno,ename order by 3;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s