Search code examples
oraclesumdecimalregexp-substr

Oracle SUM( TO_NUMBER( REGEXP_SUBSTR WITH DECIMAL NUMBER

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

Solution

  • 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;