[color=rgb(62, 62, 62)]业务人员需要对一张表中的交易数据，按照月份，统计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多秒后，却出现了如下报错：[img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6caHQIogxRd9uoYrGfNqChsseVXw6oX9DMZxMFpY7YvKEiaWxdFaGXTJ5ZEwV5lpAptvxeW86BYicKvQ/0?wx_fmt=png[/img] 报错很明确，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到94）\.表示小数点5）+则表示至少匹配一次。 综合在一起，该正则表达式就是表示，如果不是数字或小数点的字符出现，则结果匹配，也就是case表达式为真，此时将XXX_TRANS的值记为0。再次运行该sql，获得查询结果。