with t as 
(
  select '-' as col1   --isnumeric('-')这里会判断为数字,所以不能用
  union all
  select '1' as col1 
  union all 
  select '2' as col1 
  union all 
  select '3.4' as col1 
  union all 
  select 's' as col1 
)
select col1, convert(numeric(10,4), 
--                case WHEN isnumeric(col1)=1 then col1 else 0*1.0 end 
                case WHEN PATINDEX('%[^0-9|.|-|+]%',col1)=0
                  THEN col1 
                  ELSE 0*1.0
                end
                ) 
        as cc from t

 

posted on 2020-04-14 22:00  丶愤怒的蘑菇  阅读(347)  评论(0编辑  收藏  举报