Sumstring’s funny here…
March 30th, 2007 — ddelmoliGiven 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…
March 30th, 2007 at 4:04 pm
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
March 30th, 2007 at 6:27 pm
Very good — I clearly need to give out harder problems
April 5th, 2007 at 6:57 am
Can anybody please explain how this sql works ?
April 9th, 2007 at 3:00 pm
Well, rjamya, can you explain it?
April 9th, 2007 at 3:07 pm
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)