将列值从 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 会有问题吗？
谢谢！
I have a SQL Server 2000 database with a column of type VARCHAR(255). All the data is either NULL, or numeric data with up to two points of precision (e.g. '11.85'). I tried to run the following T-SQL query but received the error 'Error converting data type varchar to numeric'
SELECT CAST([MyColumn] AS DECIMAL)
FROM [MyTable];
I tried a more specific cast, which also failed.
SELECT CAST([MyColumn] AS DECIMAL(6,2))
FROM [MyTable];
I also tried the following to see if any data is non-numeric, and the only values returned were NULL.
SELECT ISNUMERIC([MyColumn]), [MyColumn]
FROM [MyTable]
WHERE ISNUMERIC([MyColumn]) = 0;
I tried to convert to other data types, such as FLOAT and MONEY, but only MONEY was successful. So I tried the following:
SELECT CAST(CAST([MyColumn] AS MONEY) AS DECIMAL)
FROM [MyTable];
...which worked just fine. Any ideas why the original query failed? Will there be a problem if I first convert to MONEY and then to DECIMAL?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这可能取决于小数符号是逗号还是点。以下是一些测试查询及其结果：
ISNUMERIC< 的文档/a> 说：
3.6和3,6都可以转换为money，所以这就是为什么isnumeric('3,6') 返回<代码>1。要解决此问题，请将逗号替换为点（或者反之亦然，如果您的系统使用逗号作为小数点符号）：
另一种选择是更改 Windows 中的“小数点符号”。它位于配置面板 ->区域和语言 ->格式->附加设置->数字。
It's likely that this depends on whether the decimal symbol is a comma or a dot. Here are some test queries and their results:
The documentation for ISNUMERIC says :
Both
3.6and3,6can be cast tomoney, so that's whyisnumeric('3,6')returns1.To resolve this issue, replace the comma's with dots (or vice versa, if your system uses comma as the decimal symbol):
Another option is to change the "decimal symbol" in Windows. It's in Config Panel -> Regional and Language -> Formats -> Additional Settings -> Numbers.
另一个原因是空字符串。例如，如果您使用导入向导导入 CSV 文件，空字段将变为空字符串，而不是空值。
这是解决此问题的常见习惯用法：
Another cause is empty strings. For example, if you use the import wizard to import a CSV file, empty fields will become empty strings, not nulls.
This is a common idiom for fixing this:
选择 CAST(isnull(MyColumn,0) 作为 Decimal(4,2))
来自[我的表]；
Select CAST(isnull(MyColumn,0) as Decimal(4,2))
FROM [MyTable];