转载 ---使用正则表达式实现四则运算的一条sql语句

 

使用正则表达式实现四则运算的一条sql语句
http://www.itpub.net/viewthread.php?action=printable&tid=1051167

create table mar_test( id number, text varchar2(200))
     
      insert into mar_Test values ( 1,'12+556-543*152/2423+23*2*435+34-234');
      insert into mar_Test values ( 2,'12/2/3/4');
      insert into mar_Test values ( 3,'1*2*34');
      insert into mar_Test values ( 4,'1+5-5*2/5+3*2*4*5+34-2');

 

算法就是
将       '12+556-543*152/2423+23*2*435+34-234'
变成2部分  '12+556-                       0+              0+34-234'
        和  -543*152/2423 ；23*2*435
分别计算求和

select a.id,
       max(text) text,
       sum(regexp_substr(add_text, '[0-9]+', 1, n) *
           decode(regexp_substr('+' || add_text, '[^0-9]', 1, n),
                  '+',
                  1,
                  -1)) +                                                                            －－加法的数字求和，乘法和除法分开计算 ，取每个子串前的运算符号，如果为 ‘－’就 乘以－1
       sum((select decode(substr(regexp_substr('+' || text,            
                                              '[+|-]([0-9]+[*|/]+)+[0-9]+',
                                              1,
                                              n),
                                1,
                                1),
                         '+',
                         1,
                         -1) *                                                                   －－ 如 25*515/544  子串前的运算符号，如果为 ‘－’就 乘以－1
                  power(10,                                                                  －－ 从itpub上学到的 利用lg将 连乘 改为加法
                        Sum(Log(10,
                                decode(regexp_substr('*' ||
                                                     regexp_substr(text,
                                                                   '([0-9]+[*|/]+)+[0-9]+',
                                                                   1,
                                                                   n),
                                                     '[^0-9]',
                                                     1,
                                                     rownum),
                                       '*',
                                       regexp_substr(regexp_substr(text,                      
                                                                   '([0-9]+[*|/]+)+[0-9]+',
                                                                   1,
                                                                   n),
                                                     '[0-9]+',
                                                     1,
                                                     rownum),
                                       1 / regexp_substr(regexp_substr(text,
                                                                       '([0-9]+[*|/]+)+[0-9]+',
                                                                       1,
                                                                       n),
                                                         '[0-9]+',
                                                         1,
                                                         rownum)))))
             from dual
           connect by rownum <= len)) wanted
  from (select a.id,
               a.text,
               length(regexp_replace(text, '[0-9]+')) + 1 len,                         －－算式中的数字个数
               regexp_replace(text, '([0-9]+[*|/]+)+[0-9]+', 0) add_text        －－将算式中 乘的子式 代替成0, 后面分开计算
          from mar_test a) a,
       (select rownum n from dual connect by rownum < 100) b                 －－默认算式最多数字100个
where a.len >= b.n
group by id

sql 实现对 text中算式的计算 ，限制就是不能加括号
运算结果
ID        TEXT                                                                        WANTED
1        12+556-543*152/2423+23*2*435+34-234        20343.93644
2        12/2/3/4                                                                        0.5
4        1+5-5*2/5+3*2*4*5+34-2                                        156
3        1*2*34                                                                        68

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30821/viewspace-442413/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30821/viewspace-442413/