ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转载 ---使用正则表达式实现四则运算的一条sql语句

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

原创 Linux操作系统 作者:admincy 时间:2019-06-20 17:48:06 0 删除 编辑

 

使用正则表达式实现四则运算的一条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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: partition outer join
下一篇: server_errno=1236
请登录后发表评论 登录
全部评论

注册时间:2018-09-17

  • 博文量
    18
  • 访问量
    17088