PL/SQL split a string by " + " and sum the numbers in it?

Question

CREATE OR REPLACE FUNCTION sumNumbers(p VARCHAR2) RETURN NUMBER IS

...

SELECT sumNumbers('3 + 6 + 13 + 0 + 1') FROM dual;

So the output should be: 23

Answer 1

You can use a 'trick' with XML and XPath evaluation to do this without manually tokenising the string:

select * from xmltable('3 + 6 + 13 + 0 + 1' columns result number path '.');

    RESULT
----------
        23

Or:

select to_number(xmlquery('3 + 6 + 13 + 0 + 1' returning content).getStringVal()) as result
from dual;

    RESULT
----------
        23

This is more flexible as other operators can be used:

select * from xmltable('2 * (5 + 7) - 3' columns result number path '.');

    RESULT
----------
        21

It doesn't like division using the normal / operator though, and you need to use div instead, so you might need to do a replace on your source string if it might contain a slash:

select * from xmltable('2 * (5 + 7) div 3' columns result number path '.');

    RESULT
----------
         8

Read more about Oracle's XPath handling, and XPath numeric operators.

You can probably call that directly, without a function; but if you particularly wanted to wrap it in a function it's a little more complicated as the XPath has to be fixed at parse time, so you would need to use dynamic SQL:

CREATE OR REPLACE FUNCTION sumNumbers(p VARCHAR2) RETURN NUMBER IS
  l_result NUMBER;
BEGIN
  execute immediate q'[select * from xmltable(']'
    || replace(p, '/', ' div ')
    || q'[' columns result number path '.')]'
  into l_result;

  return l_result;
END;
/

SELECT sumNumbers('3 + 6 + 13 + 0 + 1') FROM dual;

               SUMNUMBERS('3+6+13+0+1')
---------------------------------------
                                     23

I've used the alternative quoting mechanism to avoid escaping the single quotes in the statement, though I'm not sure it's much clearer here. And I've included the replace() in case you need to be able to divide, but it you don't want that then just concatenate p straight into the SQL statement. With the replace you can do:

SELECT sumNumbers('2 * (5 + 7) / 3') FROM dual;

If you are going to allow it to be flexible the function should have a different name...