php 在 SQL Server 中将 Varchar 值转换为整数/十进制值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16204539/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting Varchar Value to Integer/Decimal Value in SQL Server
提问by Philips Tel
How to convert the value of records in a table with data type varcharto integeror decimalso I can get the sum of the value. I use SQL Server 2005.
如何将具有数据类型的表中的记录值转换varchar为integerordecimal以便我可以获得值的总和。我用SQL Server 2005.
Say that I have table structure like this:
假设我有这样的表结构：
ID int
Stuff varchar
and the records
和记录
ID = 1, Stuff = 32.43
ID = 2, Stuff = 43.33
ID = 3, Stuff = 23.22
Now I want to get the total of column Stuff:
现在我想得到列 Stuff 的总数：
SELECT SUM(convert(decimal(4,2), Stuff)) as result FROM table
But it doesn't work. Please need help!
但它不起作用。请需要帮助！
回答by Hackerman
Table structure...very basic:
表结构...非常基本：
create table tabla(ID int, Stuff varchar (50));
insert into tabla values(1, '32.43');
insert into tabla values(2, '43.33');
insert into tabla values(3, '23.22');
Query:
询问：
SELECT SUM(cast(Stuff as decimal(4,2))) as result FROM tabla
Or, try this:
或者，试试这个：
SELECT SUM(cast(isnull(Stuff,0) as decimal(12,2))) as result FROM tabla
Working on SQLServer 2008
使用 SQLServer 2008
回答by Jose Areas
You are getting arithmetic overflow. this means you are trying to make a conversion impossible to be made. This error is thrown when you try to make a conversion and the destiny data type is not enough to convert the origin data. For example:
您正在获得算术溢出。这意味着您正在尝试进行无法进行的转换。当您尝试进行转换并且命运数据类型不足以转换原始数据时，将引发此错误。例如：
If you try to convert 100.52 to decimal(4,2) you will get this error. The number 100.52 requires 5 positions and 2 of them are decimal.
如果您尝试将 100.52 转换为十进制 (4,2)，您将收到此错误。数字 100.52 需要 5 个位置，其中 2 个是小数。
Try to change the decimal precision to something like 16,2 or higher. Try with few records first then use it to all your select.
尝试将小数精度更改为 16,2 或更高。先尝试几条记录，然后将其用于所有选择。
回答by codingbiz
The reason could be that the summation exceeded the required number of digits - 4. If you increase the size of the decimal to decimal(10,2), it should work
原因可能是总和超过了所需的位数 - 4。如果将小数的大小增加到decimal(10,2)，它应该可以工作
SELECT SUM(convert(decimal(10,2), Stuff)) as result FROM table
OR
或者
SELECT SUM(CAST(Stuff AS decimal(6,2))) as result FROM table
回答by Jon
You can use it without casting such as:
您可以在不进行强制转换的情况下使用它，例如：
select sum(`stuff`) as mySum from test;
Or cast it to decimal:
或将其转换为十进制：
select sum(cast(`stuff` as decimal(4,2))) as mySum from test;
EDIT
编辑
For SQL Server, you can use:
对于 SQL Server，您可以使用：
select sum(cast(stuff as decimal(5,2))) as mySum from test;
