I have query like this
SELECT SUM( TO_NUMBER( REGEXP_SUBSTR( '2,0,0,0,0,0,8.36,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0', '\d+', 1, LEVEL ) ) ) AS SUM_TOTAL
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( '2,0,0,0,0,0,8.36,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0', '\d+' );
AND MY RESULT IS 46, THE DECIMAL NUMBER (8.36) IS COUNTED AS SINGLE NUMBER LIKE (2 + 8 + 36) = 46.
I'M TRYING TO GET RESULT (2 + 8.36) = 10.36
IS IT POSSIBLE TO GET THIS RESULT ?
THANKS IN ADVANCE
Imstead of '\d+'
you will need to skip the comma.
Use '[^,]+'
So you query will be
I have query like this
SELECT SUM( TO_NUMBER( REGEXP_SUBSTR( '2,0,0,0,0,0,8.36,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0', '[^,]+', 1, LEVEL ) ) ) AS SUM_TOTAL
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( '2,0,0,0,0,0,8.36,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0', ',' ) + 1;