How split string to array oracle?

'3/5/7'

Result:

15

CodePudding user response：

You can use xmltable to generate rows from string :

More numbers are accepted.

select sum(to_number(column_value)) as SOMME from xmltable(REPLACE('3/5/7', '/', ','));

Demo here

CodePudding user response：

Generate dummy rows (below: SELECT ROWNUM pos FROM dual CONNECT BY LEVEL <= 10), more than the # of elements you expect to ever have. Here I used 10, you could make it 100 or 1000 if you wanted. Label each row numerically with ROWNUM. Then use that as an operand into the SUBSTR function. This can be done with SUBSTR_REGEXP too perhaps more concisely. This will tokenize your string. Then all you have to do is aggregate the results with SUM.

SELECT SUM(item)
  FROM (SELECT value,
               pos,
               SUBSTR(value,INSTR(value,'/',1,pos) 1,INSTR(value,'/',1,pos 1)-(INSTR(value,'/',1,pos) 1)) item
          FROM (SELECT '/'||'3/5/7'||'/' value from dual),
               (SELECT ROWNUM pos FROM dual CONNECT BY LEVEL <= 10))
 WHERE item IS NOT NULL