7 Replies Latest reply: Jun 8, 2008 2:47 PM by 598210 RSS

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

    598210
      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.
        • 1. Re: How to break a number into pieces and get the sum or product of the num
          RPuttagunta
          The reason this is not working is the reason this would not work.

          XXDAN@TST > select to_number('1*2*3*4*5*6*1') from dual;
          select to_number('1*2*3*4*5*6*1') from dual
          *
          ERROR at line 1:
          ORA-01722: invalid number


          The to_number function's input is a string called '1*2*3*4*5*6*1' and this itself cannot be cast to a number.

          Although, I have have shown you why this is not working, I couldn't come up with the answer you are looking for at the top of my head.

          Message was edited by:
          RPuttagunta
          • 2. Re: How to break a number into pieces and get the sum or product of the num
            486393
            select to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select '||regexp_replace(123456,'(.)','\1+') || '0'||'  s from dual')),'/ROWSET/ROW/S')) v from dual
            union all 
            select to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select '||regexp_replace(123456,'(.)','\1*') || '1'||'  p from dual')),'/ROWSET/ROW/P')) v from dual
            /
            • 3. Re: How to break a number into pieces and get the sum or product of the num
              Rob van Wijk
              On 11g you can use this:
              SQL>  select level
                2        , regexp_replace(level,'(.)','\1+') || '0' sum
                3        , xmlquery(regexp_replace(level,'(.)','\1+') || '0' returning content).getNumberVal() sum_evaluated
                4        , regexp_replace(level,'(.)','\1*') || '1' product
                5        , xmlquery(regexp_replace(level,'(.)','\1*') || '1' returning content).getNumberVal() product_evaluated
                6     from dual
                7  connect by level <= 100
                8  /

              LEVEL SUM                  SUM_EVALUATED PRODUCT              PRODUCT_EVALUATED
              ------ -------------------- ------------- -------------------- -----------------
                   1 1+0                              1 1*1                                  1
                   2 2+0                              2 2*1                                  2
                   3 3+0                              3 3*1                                  3
                   4 4+0                              4 4*1                                  4
                   5 5+0                              5 5*1                                  5
                   6 6+0                              6 6*1                                  6
                   7 7+0                              7 7*1                                  7
                   8 8+0                              8 8*1                                  8
                   9 9+0                              9 9*1                                  9
                  10 1+0+0                            1 1*0*1                                0
                  11 1+1+0                            2 1*1*1                                1
                  12 1+2+0                            3 1*2*1                                2
                  13 1+3+0                            4 1*3*1                                3
                  14 1+4+0                            5 1*4*1                                4
                  15 1+5+0                            6 1*5*1                                5
                  16 1+6+0                            7 1*6*1                                6
                  17 1+7+0                            8 1*7*1                                7
                  18 1+8+0                            9 1*8*1                                8
                  19 1+9+0                           10 1*9*1                                9
                  20 2+0+0                            2 2*0*1                                0
                  21 2+1+0                            3 2*1*1                                2
                  22 2+2+0                            4 2*2*1                                4
                  23 2+3+0                            5 2*3*1                                6
                  24 2+4+0                            6 2*4*1                                8
                  25 2+5+0                            7 2*5*1                               10
                  26 2+6+0                            8 2*6*1                               12
                  27 2+7+0                            9 2*7*1                               14
                  28 2+8+0                           10 2*8*1                               16
                  29 2+9+0                           11 2*9*1                               18
                  30 3+0+0                            3 3*0*1                                0
                  31 3+1+0                            4 3*1*1                                3
                  32 3+2+0                            5 3*2*1                                6
                  33 3+3+0                            6 3*3*1                                9
                  34 3+4+0                            7 3*4*1                               12
                  35 3+5+0                            8 3*5*1                               15
                  36 3+6+0                            9 3*6*1                               18
                  37 3+7+0                           10 3*7*1                               21
                  38 3+8+0                           11 3*8*1                               24
                  39 3+9+0                           12 3*9*1                               27
                  40 4+0+0                            4 4*0*1                                0
                  41 4+1+0                            5 4*1*1                                4
                  42 4+2+0                            6 4*2*1                                8
                  43 4+3+0                            7 4*3*1                               12
                  44 4+4+0                            8 4*4*1                               16
                  45 4+5+0                            9 4*5*1                               20
                  46 4+6+0                           10 4*6*1                               24
                  47 4+7+0                           11 4*7*1                               28
                  48 4+8+0                           12 4*8*1                               32
                  49 4+9+0                           13 4*9*1                               36
                  50 5+0+0                            5 5*0*1                                0
                  51 5+1+0                            6 5*1*1                                5
                  52 5+2+0                            7 5*2*1                               10
                  53 5+3+0                            8 5*3*1                               15
                  54 5+4+0                            9 5*4*1                               20
                  55 5+5+0                           10 5*5*1                               25
                  56 5+6+0                           11 5*6*1                               30
                  57 5+7+0                           12 5*7*1                               35
                  58 5+8+0                           13 5*8*1                               40
                  59 5+9+0                           14 5*9*1                               45
                  60 6+0+0                            6 6*0*1                                0
                  61 6+1+0                            7 6*1*1                                6
                  62 6+2+0                            8 6*2*1                               12
                  63 6+3+0                            9 6*3*1                               18
                  64 6+4+0                           10 6*4*1                               24
                  65 6+5+0                           11 6*5*1                               30
                  66 6+6+0                           12 6*6*1                               36
                  67 6+7+0                           13 6*7*1                               42
                  68 6+8+0                           14 6*8*1                               48
                  69 6+9+0                           15 6*9*1                               54
                  70 7+0+0                            7 7*0*1                                0
                  71 7+1+0                            8 7*1*1                                7
                  72 7+2+0                            9 7*2*1                               14
                  73 7+3+0                           10 7*3*1                               21
                  74 7+4+0                           11 7*4*1                               28
                  75 7+5+0                           12 7*5*1                               35
                  76 7+6+0                           13 7*6*1                               42
                  77 7+7+0                           14 7*7*1                               49
                  78 7+8+0                           15 7*8*1                               56
                  79 7+9+0                           16 7*9*1                               63
                  80 8+0+0                            8 8*0*1                                0
                  81 8+1+0                            9 8*1*1                                8
                  82 8+2+0                           10 8*2*1                               16
                  83 8+3+0                           11 8*3*1                               24
                  84 8+4+0                           12 8*4*1                               32
                  85 8+5+0                           13 8*5*1                               40
                  86 8+6+0                           14 8*6*1                               48
                  87 8+7+0                           15 8*7*1                               56
                  88 8+8+0                           16 8*8*1                               64
                  89 8+9+0                           17 8*9*1                               72
                  90 9+0+0                            9 9*0*1                                0
                  91 9+1+0                           10 9*1*1                                9
                  92 9+2+0                           11 9*2*1                               18
                  93 9+3+0                           12 9*3*1                               27
                  94 9+4+0                           13 9*4*1                               36
                  95 9+5+0                           14 9*5*1                               45
                  96 9+6+0                           15 9*6*1                               54
                  97 9+7+0                           16 9*7*1                               63
                  98 9+8+0                           17 9*8*1                               72
                  99 9+9+0                           18 9*9*1                               81
                 100 1+0+0+0                          1 1*0*0*1                              0

              100 rijen zijn geselecteerd.
              which doesn't work on 10.2 unfortunately. And I'm not aware of any other method to do a dynamic evaluation in SQL in that version.

              Regards,
              Rob.
              • 4. Re: How to break a number into pieces and get the sum or product of the num
                486393
                or
                create or replace function calculate(p_expression in varchar2)
                return number
                is
                  l_result number;
                begin
                  execute immediate ' select '||p_expression||' from dual  ' into l_result;
                  return l_result;
                end;
                /
                SQL> select calculate('1*2*3*4*5*6') from dual;
                
                CALCULATE('1*2*3*4*5*6')
                ------------------------
                                     720
                • 5. Re: How to break a number into pieces and get the sum or product of the num
                  Rob van Wijk
                  This gives a ORA-03113 on my 10.2.0.1 instance.
                  What version did you try that on?

                  Groet,
                  Rob.
                  • 6. Re: How to break a number into pieces and get the sum or product of the num
                    486393
                    @Rob

                    my

                    extractvalue(xmltype(dbms_xmlgen.getxml(....

                    solution works in Oracle 11.

                    SQL> select * from v$version;

                    BANNER
                    --------------------------------------------------------------------------
                    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                    PL/SQL Release 11.1.0.6.0 - Production
                    CORE 11.1.0.6.0 Production
                    TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
                    NLSRTL Version 11.1.0.6.0 - Production

                    But I get error ORA-03113 in

                    ----------------------------------------------------------------
                    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
                    PL/SQL Release 10.2.0.1.0 - Production
                    CORE 10.2.0.1.0 Production
                    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
                    NLSRTL Version 10.2.0.1.0 - Production

                    I can do this in Oracle 10.2:

                    select extractvalue(xmltype(dbms_xmlgen.getxml('select 1+2+3+4+5+6 s from dual')),'/ROWSET/ROW
                    from dual;

                    Maybe it is the regexp_replace that causes the trouble in Oracle 10.2 ?
                    • 7. Re: How to break a number into pieces and get the sum or product of the num
                      598210
                      Rob van Wijk and wateenmooiedag thank you for your help.

                      wateenmooiedag solution works for me but it is slowe compared to a simple user defined pl/sql function, I also mentioned in this thread.

                      SQL puzzle :)