how split string to array oracle?

Question

How split string to array oracle?

'3/5/7'

Result:

Would the strings always have exactly three numbers? Can you add more sample data? Note that Oracle formerly doesn't really support arrays. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 2, 2023 at 12:04

SelVazi · Accepted Answer · 2023-02-02 12:22:51Z

2

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

edited Feb 2, 2023 at 12:22

answered Feb 2, 2023 at 12:12

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul W · Accepted Answer · 2023-02-02 12:18:42Z

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

Collectives™ on Stack Overflow

how split string to array oracle?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related