oracle正则查询浮点数,巧用Oracle正则表达式解决查询问题

业务人员需要对一张表中的交易数据，按照月份，统计2015年6月到2016年4月分月的交易笔数和总的交易金额。需求很简单，也非常明确，sql实现也非常简单。考虑到涉及的表比较大，有好几十个G，因此查询时采用了并行处理：

select /*+ parallel(t 18) */

substr(t.XXX_DATE, 1, 6), sum(t.XXX_TRANS), count(*)

from xxx_yyyy_txn_his t

where to_date(t.XXX_DATE, 'yyyymmdd') >= date

'2015-06-01't.XXX_TRANS

and to_date(t.XXX_DATE, 'yyyymmdd') < date '2016-04-13'

group by substr(t.XXX_DATE, 1, 6);

实际运行上述sql，但是在运行10多秒后，却出现了如下报错：

报错很明确，ORA-01722表示有无效数字。看到该报错后，立即检查了表结构。出乎意料，交易金额字段XXX_TRANS确实使用的是VARCHAR2类型。至此，可以比较肯定的是，上述的sql运行时，sum(t.XXX_TRANS)时，oracle进行了隐式转换，将字符转为了数字进行数学运算。但是由于交易金额字段可能存在了非数字字符，因此隐式转换时报了ORA-01722错误。如果要使得上述sql能够正常运行，则必须找出有问题的记录。但是由于表中的记录数目非常多，如果逐一排查记录，找出有问题的记录，则需要耗费太多的时间。

由于业务人员只需要大概的数字，不需要完全精确的结果，因此考虑使用case进行判断后，然后取值。但是由于该字段包含的字符不确定，所以使用普通的like判断无法实现需求。好在Oracle提供了正则表达式，可以对XXX_TRANS进行基于正则表达式的判断，然后再确定其值，即：如果此字段包含非数字字符(不包括小数点)，则将结果记为0，否则使用原来的值。按照该想法，sql如下：

select /*+ parallel(t 18) */

substr(t.XXX_DATE, 1, 6),

sum(case

when regexp_like(t.XXX_TRANS, '[^0-9\.]+') then

'0'

else

t.XXX_TRANS

end),

count(*)

from xxx_yyyy_txn_his t

where to_date(t.XXX_DATE, 'yyyymmdd') >= date

'2015-06-01'

and to_date(t.XXX_DATE, 'yyyymmdd') < date '2016-04-13'

group by substr(t.XXX_DATE, 1, 6) order by 1 asc

其中红色部分即为正则表达式，各项解释如下：

1)[]表示一个集合，

2)^则表示对后面的字符集合取反，

3)0-9表示数字0到9

4)\.表示小数点

5)+则表示至少匹配一次。

综合在一起，该正则表达式就是表示，如果不是数字或小数点的字符出现，则结果匹配，也就是case表达式为真，此时将XXX_TRANS的值记为0。再次运行该sql，获得查询结果。