Skip to Main Content

How to break a number into pieces and get the sum or product of the numbers

598210Jun 8 2008 — edited Jun 8 2008
on 10.2 I tried to use regular expression but TO_NUMBER function does not work with REGEXP_REPLACE as below;
SQL> SELECT regexp_replace(123456,
  2                        '(.)',
  3                        '\1+') || '0' RESULT
  4    FROM dual;

RESULT
-------------
1+2+3+4+5+6+0

SQL> SELECT 1+2+3+4+5+6+0 RESULT FROM dual;

    RESULT
----------
        21

SQL> SELECT regexp_replace(123456,
  2                        '(.)',
  3                        '\1*') || '1' RESULT
  4    FROM dual;

RESULT
-------------
1*2*3*4*5*6*1

SQL> SELECT 1*2*3*4*5*6*1 RESULT FROM dual;

    RESULT
----------
       720
I recieve ORA-01722: invalid number as below;
SQL> SELECT to_number(regexp_replace(123456,
  2                        '(.)',
  3                        '\1+') || '0') RESULT
  4    FROM dual;

SELECT to_number(regexp_replace(123456,
                      '(.)',
                      '\1+') || '0') RESULT
  FROM dual

ORA-01722: invalid number
Any comments? Thank you.
Comments
Post Details
Added on Jun 8 2008
7 comments
2,443 views