sql - sum the contents of a delimted column in an oracle table column

Question

I have a column in an oracle database table which has amounts comma separated. I have a requirement to sum the comma separated numerics and display.

Stored procedure could be an option. After much googling I have found the below solution as well for columns say, COL1, COL2, COL3, AMOUNT. Here AMOUNT is comma separated as below,

123423,23523,5454,242342

Solution found after much search,

select 
  a.COL1 as COL1,
  a.COL2 as COL2,
  a.COL2 as COL3,
  sum(REGEXP_SUBSTR(a.AMOUNT, 'd+', 1, occ)) as SUM
from 
  TBL1 a,
  (select level occ from DUAL connect by level < 3000) 
 group by a.COL1, a.COL2, a.COL3;

But the problem here is this is a huge table and because of the group by, it is taking a long time. Since we are summing up the data in an individual column value of a single row and not aggregating with other rows, is there a performing way of doing the same?

Answer 1

The bad design of this table has already caused you problems, but this solution might work:

SELECT 
  a.COL1 as COL1,
  a.COL2 as COL2,
  a.COL2 as COL3,
  (
      SELECT SUM(trim(regexp_substr(amount, '[^,]+', 1, LEVEL)))
      FROM dual
      CONNECT BY regexp_substr(amount , '[^,]+', 1, LEVEL) IS NOT NULL
  ) as AMOUNT
FROM tbl1 a;