SQL 将 varchar 列转换为浮点数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22353371/
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
SQL convert varchar column to float
提问by JPK
An importing tool put every column in several tables as varchar(max) and I am looking for the fastest way to convert the columns to the proper type.
导入工具将多个表中的每一列作为 varchar(max) 放入，我正在寻找将列转换为正确类型的最快方法。
This works fine,
这工作正常，
ALTER TABLE dbo.myTable ALTER COLUMN myColumn INT
but this fails miserably
但这很失败
ALTER TABLE dbo.myTable ALTER COLUMN myColumn FLOAT
With error: Msg 8114, Level 16, State 5, Line 26 Error converting data type varchar to float.
有错误：消息 8114，级别 16，状态 5，第 26 行将数据类型 varchar 转换为浮点数时出错。
How do I perform a generic column conversion from type VarChar(max) to Float?
如何执行从 VarChar(max) 类型到 Float 类型的通用列转换？
I found some hints in these posts, but I have not been able to get it to convert:
我在这些帖子中找到了一些提示，但我无法将其转换：
Convert varchar to float IF ISNUMERIC
将 varchar 转换为 float IF ISNUMERIC
Error converting data type varchar
error converting varchar to float
select case isnumeric([myColumn]) when 1 then cast([myColumn] as float) else null end
from dbo.myTable
and
和
SELECT TOP (100) CAST(CASE WHEN IsNumeric([myColumn]) = 1 THEN [myColumn] ELSE NULL END AS float) AS [myColumn]
FROM dbo.myTable
WHERE ([myColumn] NOT LIKE '%[^0-9]%')
It seems there is some issues with ISNUMERIC?
ISNUMERIC 似乎有些问题？
I would prefer not to do it like this as there is a lot of columns to edit, is there a single line conversion like ALTER TABLE that works in most or all scenarios?
我不想这样做，因为有很多列要编辑，是否有像 ALTER TABLE 这样的单行转换适用于大多数或所有场景？
Alternatively someone has suggested holding the data into a temp column? If someone could post code to do this into a tempColumn and then convert the original and delete the tempColumn that would be another valid solution.
或者有人建议将数据保存到临时列中？如果有人可以将代码发布到 tempColumn 中，然后转换原始代码并删除将是另一个有效解决方案的 tempColumn。
回答by ericpap
Are the decimal separator of the system configured the same way as content from varchar field? In your case i will advice you to créate a new column(FLOAT) instead of alter existing column. Then fill that field with an update.
系统的小数点分隔符是否与 varchar 字段的内容配置方式相同？在您的情况下，我会建议您创建一个新列（FLOAT）而不是更改现有列。然后用更新填充该字段。
Here is a way to explicit convert text to float. IN SQL Server this query :
这是一种将文本显式转换为浮动的方法。在 SQL Server 这个查询中：
select cast(replace('12,5',',','.') as float)*2
Resutns 25 as response. That means is converted successfull to FLOAT
结果 25 作为响应。这意味着成功转换为 FLOAT
So to fill you new col with casted values you could do:
因此，要使用铸造值填充您的新 col，您可以执行以下操作：
UPDATE Table SET FloatField=CAST(REPLACE(TextField,',','.') AS FLOAT)
That replace the , for . (In case there is any) and then converts to FLOAT. Hope this help.
替换 , for 。（如果有的话）然后转换为 FLOAT。希望这有帮助。
