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.