Sumstring’s funny here…

Given an arbitrary string of positive numbers separated by spaces, how can I produce the sum of the numbers using only SQL?  There can be any number of numbers in the string.

Examples:

’10543 959 20453′ should produce 31955
’4859 0 0 0 12314′ should produce 17173
’0 10 15        78      104′ should produce 207

Preferred solution uses Oracle 9i and does not require any PL/SQL functions…

5 Responses to “Sumstring’s funny here…”

  1. rjamya Says:

    With code example from http://www.jlcomp.demon.co.uk/faq/Split_Strings.html

    select sum(nums) from(
    SELECT SUBSTR(str,
    INSTR(str, ‘ ‘, 1, LEVEL ) + 1,
    INSTR(str, ‘ ‘, 1, LEVEL+1) -
    INSTR(str, ‘ ‘, 1, LEVEL) -1 ) nums
    FROM (SELECT rownum r, ‘ ‘||’10543 959 20453 25 666 7789′||’ ‘ AS str FROM dual) — this is where the string goes
    CONNECT BY PRIOR r = r
    AND INSTR (str, ‘ ‘, 1, LEVEL+1) > 0
    AND PRIOR dbms_random.string (‘p’, 100) IS NOT NULL)
    /

    All credit goes to Frank Zhou who posted the snippet. I just made minor changes to make it work.
    HTHs
    rjamya

  2. ddelmoli Says:

    Very good — I clearly need to give out harder problems :-)

  3. Narendra Says:

    Can anybody please explain how this sql works ?

  4. ddelmoli Says:

    Well, rjamya, can you explain it?

  5. ddelmoli Says:

    I think this solution is a bit easier to understand:

    select sum((substr(str,instr(str,’ ‘,1,level)+1,instr(str,’ ‘,1,level+1)-instr(str,’ ‘,1,level)-1))) tot
    from (select ‘ ‘||’10543 959 20453 25 666 7789′||’ ‘ str from dual)
    connect by level <= length(str)

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder