select max(to_number(goods)) from ( select case when regexp_like(replace(goods_no,‘A05‘,‘‘),‘[A-Za-z]‘) then ‘0‘ else replace(goods_no,‘A05‘,‘‘) end goods,goods_no from tb_b_goods where goods_no like ‘A05%‘ ) aa where goods!=0 ;
原文：http://www.cnblogs.com/liang-ling/p/4242484.html