赏100分的，在线等

Question

现在遇上的问题是表price中有item_price1、item_price2、item_price3这三个字符型字段，记录的是商品的三个不同价格，里面有些记录有错误，比如某些字段内容为304.5.5或56.4.4（应该是最初物价处做价表时录错了，输了两个小数点，正确值应该是304.5，56.4），某些字段内容带有一些特殊字符（不可见，但一转换为数值型就会报错）,这些错误导致更新价格时报错。并且这些错误没有规律，难以在1.8万条记录中一个个查找，现在请大家思考一下，能否有办法快速、简洁查找出这三个字段中数据有错误、无法转换为数值的记录。

Answer 1

做了个例子,你改一下把,大体这样,不知道能不能简化..
SQL> select * from a1;A
----------
1
1.1
1.1.1
as
1a
a1已选择6行。SQL> select a from a1 where nvl2(replace(translate(a,'.0123456789','000000000000
'),'0',''),'notNum','isNUm')='notNum'
  2  union all
  3  select a from a1 where lengthb(a)-lengthb(replace(a,'.',''))>1;A
----------
as
1a
a1
1.1.1SQL>

Answer 2

select a from a1 where nvl2(replace
   (translate(a,'.0123456789','000000000000'),'0',''),
   'notNum','isNUm')='notNum'
union all
select a from a1 where lengthb(a)-lengthb(replace(a,'.',''))>1;

Answer 3

针对PL/SQL修正了一下，对item_price2的完整判断以及立即修正方式
select *
  from comm.new_price_end
 where item_code in
       (select item_code
          from comm.new_price_end
         where nvl2(replace(translate(item_price2,'.0123456789', '000000000000'), '0', ''), 'notNum', 'isNUm') = 'notNum'
        union all
        select item_code
         from comm.new_price_end
         where lengthb(item_price2) - lengthb(replace(item_price2, '.', '')) > 1
         or item_price2 = '.')
   for update;

Answer 4

如果有'#'、'%'、'&'等这样的特殊字符怎么办？

Answer 5

如果是oracle 则可以用正则表达式:
select * from
comm.new_price_end
where (not REGEXP_LIKE(item_price1,'^\d+(\.\d{0,2})?$')) or
(not REGEXP_LIKE(item_price2,'^\d+(\.\d{0,2})?$')) or
(not REGEXP_LIKE(item_price3,'^\d+(\.\d{0,2})?$'))

Answer 6

一样可以处理'#'、'%'、'&'等这样的特殊字符