在 SQL Server 中将 varchar 转换为数字类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25275202/
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 to numeric type in SQL Server
提问by CodeNinja
I have a column in a table with a varchardatatype. It has 15 digits after the decimal point. Now I am having a hard time converting it to a numeric format.. float, double etc.
我在表中有一个varchar数据类型的列。小数点后有 15 位数字。现在我很难将其转换为数字格式..浮点数、双精度数等。
Does anyone have any suggestions?
有没有人有什么建议？
Example :
例子 ：
Table1
表格1
Column1
-------------------
-28.851540616246499
-22.857142857142858
-26.923076923076923
76.19047619047619
I tried using the following statements and it doesn't seem to work :
我尝试使用以下语句，但似乎不起作用：
update table1
set Column1 = Convert(float,column1)..
Any suggestions ?
有什么建议 ？
回答by Tanner
You can use the decimal data typeand specify the precision to state how many digits are after the decimal point. So you could use decimal(28,20)for example, which would hold 28 digits with 20 of them after the decimal point.
您可以使用小数数据类型并指定精度来说明小数点后有多少位数字。decimal(28,20)例如，您可以使用它来保存 28 位数字，其中小数点后有 20 位。
Here's a SQL Fiddle, showing your data in decimal format.
这是一个SQL Fiddle，以十进制格式显示您的数据。
Fiddle sample:
小提琴样本：
create table Table1(MyValues varchar(100))
insert into Table1(MyValues)
values
('-28.851540616246499'),
('-22.857142857142858'),
('-26.923076923076923'),
('76.19047619047619')
So the values are held as varcharin this table, but you can cast it to decimal as long as they are all valid values, like so:
因此，这些值varchar在此表中保留，但只要它们都是有效值，您就可以将其转换为十进制，如下所示：
select cast(MyValues as decimal(28,20)) as DecimalValues
from table1
Your Sample
您的样品
Looking at your sample update statement, you wouldn't be able to convert the values from varcharto a numeric type and insert them back in to the same column, as the column is of type varchar. You would be better off adding a new column with a numeric data type and updating that.
查看您的示例更新语句，您将无法将值转换varchar为数字类型并将它们插入到同一列中，因为该列的类型为varchar。您最好添加一个具有数字数据类型的新列并更新它。
So if you had 2 columns:
因此，如果您有 2 列：
create table Table1(MyValues varchar(100), DecimalValues decimal(28,20))
You could do the below to update the numeric column with the nvarcharvalues that have been cast to decimal:
您可以执行以下操作以使用nvarchar已转换为十进制的值更新数字列：
update Table1
set DecimalValues = cast(MyValues as decimal(28,20))
回答by Brian DeMilia
I think you're trying to actually change the data type of that column?
我认为您正在尝试实际更改该列的数据类型？
If that is the case you want to ALTER the table and change the column type over to float, like so:
如果是这种情况，您想更改表并将列类型更改为浮动，如下所示：
alter table table1
alter column column1 float
See fiddle: http://sqlfiddle.com/#!6/637e6/1/0
见小提琴：http://sqlfiddle.com/#!6/ 637e6/1/0
You would use CONVERT if you're changing the text values to numbers for temporary use within a query (not to actually permanently change the data).
如果您将文本值更改为数字以在查询中临时使用（而不是实际永久更改数据），您将使用 CONVERT。
