Question

在sql server数据库中将一个nvarchar类型的空值转换成decimal(18,3)类型

在数据库中，字段DescFlexField_PrivateDescSeg3为nvarchar类型，在进行数据录入的时候，会有3种情况，第一种是‘18.9g’，第二种是‘18.3’，第三种就是为空，我要把这个字段转换成decimal(18,3)类型进行数据汇总，现在我能解决的是把g去掉，但是空类型的就没有办法解决。
我的SQL语句是：
select convert(decimal(18,2),
case when charindex('g',isnull(DescFlexField_PrivateDescSeg3,''))>0
then SUBSTRING(isnull(DescFlexField_PrivateDescSeg3,''),0,LEN(isnull(DescFlexField_PrivateDescSeg3,'')))
else DescFlexField_PrivateDescSeg3
end
)
from sm_so
运行结果为：从数据类型 nvarchar 转换为 numeric 时出错。
所有可以进行空值判断的我都有试过，isnull(DescFlexField_PrivateDescSeg3,'')....，但都没有用

Answer 1

select
case when charindex('g',isnull(DescFlexField_PrivateDescSeg3,''))=0 then cast(0 as decimal(18,3))
when charindex('g',isnull(DescFlexField_PrivateDescSeg3,''))>0
then cast(SUBSTRING(isnull(DescFlexField_PrivateDescSeg3,''),0,LEN(isnull(DescFlexField_PrivateDescSeg3,''))) as decimal(18,3))
else cast(DescFlexField_PrivateDescSeg3 as decimal(18,3))
end
from sm_so ,每个字段都进行一次类型转换

Answer 2

select convert(decimal(18,2),
case when DescFlexField_PrivateDescSeg3 is null then 0.0
when charindex('g',isnull(DescFlexField_PrivateDescSeg3,''))>0
then SUBSTRING(isnull(DescFlexField_PrivateDescSeg3,''),0,LEN(isnull(DescFlexField_PrivateDescSeg3,'')))
else DescFlexField_PrivateDescSeg3
end
)
from sm_so

追问

这种方法我也写过了，没有用的，报同样的错

追答

那就有可能是空字符串了，不是NULL值
select convert(decimal(18,2),
case when DescFlexField_PrivateDescSeg3 is null or DescFlexField_PrivateDescSeg3='' then 0.0
when charindex('g',isnull(DescFlexField_PrivateDescSeg3,''))>0
then SUBSTRING(isnull(DescFlexField_PrivateDescSeg3,''),0,LEN(isnull(DescFlexField_PrivateDescSeg3,'')))
else DescFlexField_PrivateDescSeg3
end
)
from sm_so

Answer 3

isnull(DescFlexField_PrivateDescSeg3,0)

追问

写过了，没有用

Answer 4

在sql中做个空值判断