欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

一个字符串转数字的小功能

程序员文章站 2022-04-14 16:05:58
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 ......
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