SQL Server:将错误数字转换为varchar的情况 [英] SQL Server : conversion error numeric to varchar case

问题描述

我有这个查询:

  CASE ClaimsFees.TBA当0 THEN CAST(IndemnityReserve AS NUMERIC(9,2))时ELSE'TBA'结束为保留赔偿金" 

但我总是会收到此错误:

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

我尝试将TBA转换为数字，但是我不能这样做，也无法将所有结果都转换为 varchar ，因为当我传输到Excel文件时，数字1.325,27变成了132.527,00，采用##，## 0.00格式.

SQL Server中是否可以使用一种方法来解决此问题?

解决方案

该列只能具有一种类型，并且由于"TBA"只能是字符串，因此您也需要将数字也设置为字符串.

  CASE ClaimsFees.TBA，时间为0然后强制转换(cast(IndemnityReserve as NUMERIC(9,2))as varchar(12))其他"TBA"以保留赔偿金"结尾， 

I have this query:

CASE ClaimsFees.TBA 
   WHEN 0 THEN CAST(IndemnityReserve AS NUMERIC(9,2)) 
   ELSE 'TBA' 
END AS 'Reserve Indemnity'

but I always get this error:

Error converting data type varchar to numeric

I have tried to convert TBA as numeric but I can't do this, I also can't convert all the results to varchar because when I transfer to Excel file the number 1.325,27 becomes 132.527,00 with the ##,##0.00 format.

Is there a method in SQL Server that I can use to solve this?

解决方案

The column can only have one type, and as 'TBA' can only be a string, you'll need to make the numeric a string too.

CASE ClaimsFees.TBA WHEN 0 
then cast(cast(IndemnityReserve as NUMERIC(9,2)) as varchar(12))
else 'TBA' 
end as 'Reserve Indemnity',

这篇关于SQL Server:将错误数字转换为varchar的情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！