将列值从 VARCHAR(n) 转换为 DECIMAL 时出现问题

我有一个 SQL Server 2000 数据库，其中有一列类型为 VARCHAR(255)。所有数据要么为 NULL，要么为最多两点精度的数字数据（例如“11.85”）。我尝试运行以下 T-SQL 查询，但收到错误“将数据类型 varchar 转换为数字时出错”

SELECT CAST([MyColumn] AS DECIMAL)
FROM [MyTable];

我尝试了更具体的演员阵容，但也失败了。

SELECT CAST([MyColumn] AS DECIMAL(6,2))
FROM [MyTable];

我还尝试了以下操作来查看是否有任何数据是非数字的，并且返回的唯一值是 NULL。

SELECT ISNUMERIC([MyColumn]), [MyColumn]
FROM [MyTable]
WHERE ISNUMERIC([MyColumn]) = 0;

我尝试转换为其他数据类型，例如FLOAT和MONEY，但只有MONEY成功。所以我尝试了以下方法：

SELECT CAST(CAST([MyColumn] AS MONEY) AS DECIMAL)
FROM [MyTable];

...效果很好。原始查询失败的原因有什么想法吗？如果我先转换为 MONEY 再转换为 DECIMAL 会有问题吗？

Thanks!

这可能取决于小数符号是逗号还是点。以下是一些测试查询及其结果：

select CAST('3.6' as decimal) -- 3.60
select CAST('3,6' as decimal) -- Error converting data type varchar to numeric.
select CAST('3.6' as money) -- 3.60
select CAST('3,6' as money) -- 36.00 (!)
select ISNUMERIC('3.6') -- 1
select ISNUMERIC('3,6') -- 1

的文档ISNUMERIC http://msdn.microsoft.com/en-us/library/aa933213(SQL.80).aspx says :

当输入时 ISNUMERIC 返回 1 表达式的计算结果为有效 整数、浮点数、金钱 或十进制类型；否则它返回 0

Both 3.6 and 3,6可以投射到money， 所以这就是为什么isnumeric('3,6')回报1.

要解决此问题，请将逗号替换为点（或者反之亦然，如果您的系统使用逗号作为小数点符号）：

select cast(replace('3,6',',','.') as decimal)

另一种选择是更改 Windows 中的“十进制符号”。它位于配置面板 -> 区域和语言 -> 格式 -> 其他设置 -> 数字。