Oracle SUM( TO_NUMBER( REGEXP_SUBSTR WITH DECIMAL NUMBER

Question

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

Answer 1

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;

Answer 2

If you are certain that your string is a comma-separated list of numbers, including decimal numbers, negative numbers, numbers in exponential notation etc., possibly surrounded by whitespace, you can modify the query by using [^,]+ instead of \d+ (in both places). This selects all substrings of consecutive non-comma characters at a time, instead of all substrings of consecutive digits at a time.