将数据类型nvarchar转换为数字时出错

Question

我的表数据

[/td>

Amount1	Amount2	预期结果
100	200	4.17%
A	500
500	B
20	100	1.67%

如果你有一个文本字段，大部分是数字，但你不能依赖它，那么就用它来代替

cast(Amount1 AS decimal(18,4))

你可以使用

try_cast(Amount1 AS decimal(18,4))

如果无法完成转换，则返回Null

我使用try_convert，但这只是个人偏好，它们实际上是相同的

try_convert(十进制(18,4(，Amount1(

所以你声明的开头看起来像这个

case 
when try_cast(Amount1 as decimal(18,4))  is not null --Numeric 
and try_cast(Amount2 as decimal(18,4)) > 0 --Numeric and not zero
then Concat(CAST((try_cast(Amount1 AS decimal(18,4))/(try_cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') 

在执行任何操作之前，请检查列是否为数字：

SELECT Amount1
, Amount2
, case when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') 
else 
null
end AS "Expected Result"
FROM tblData 

这是一个演示

如果你的数据中有0，那么如下所示：

SELECT Amount1, Amount2, 
case when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 and Amount2 > 0 and Amount1 > 0 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') 
when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1  and Amount1 = 0 and Amount1 > 0 then
Concat(CAST((cast(1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') 
when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1  and Amount1 > 0 and Amount1 = 0 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(1 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') 
else 
null
end AS "Expected Result"
FROM tblData 

这是的第二个演示