SQL 将 varchar 列转换为浮点数 [英] SQL convert varchar column to float

问题描述

导入工具将多个表中的每一列作为 varchar(max) 放入，我正在寻找将列转换为正确类型的最快方法.

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.

这很好用，

ALTER TABLE dbo.myTable ALTER COLUMN myColumn INT

但这很失败

ALTER TABLE dbo.myTable ALTER COLUMN myColumn FLOAT

有错误:Msg 8114, Level 16, State 5, Line 26将数据类型 varchar 转换为 float 时出错.

With error: Msg 8114, Level 16, State 5, Line 26 Error converting data type varchar to float.

如何执行从 VarChar(max) 类型到 Float 类型的通用列转换?

How do I perform a generic column conversion from type VarChar(max) to Float?

我在这些帖子中找到了一些提示，但我无法将其转换:

I found some hints in these posts, but I have not been able to get it to convert:

将 varchar 转换为 float IF ISNUMERIC

转换数据类型 varchar 时出错

将 varchar 转换为浮点时出错

select case isnumeric([myColumn]) when 1 then cast([myColumn] as float) else null end
from dbo.myTable

和

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]%')

ISNUMERIC 似乎有问题?

It seems there is some issues with ISNUMERIC?

我不想这样做，因为有很多列要编辑，是否有像 ALTER TABLE 这样的单行转换适用于大多数或所有场景?

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?

或者有人建议将数据保存到临时列中?如果有人可以将代码发布到 tempColumn 中，然后转换原始代码并删除将是另一个有效解决方案的 tempColumn.

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.

推荐答案

系统的小数点分隔符是否与 varchar 字段的内容配置方式相同?在您的情况下，我会建议您创建一个新列(FLOAT)而不是更改现有列.然后用更新填充该字段.

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.

这是一种将文本显式转换为浮动的方法.在 SQL Server 中此查询:

Here is a way to explicit convert text to float. IN SQL Server this query :

select cast(replace('12,5',',','.') as float)*2

结果 25 作为响应.这意味着成功转换为 FLOAT

Resutns 25 as response. That means is converted successfull to FLOAT

所以为了用你可以做的转换值填充你的新列:

So to fill you new col with casted values you could do:

UPDATE Table SET FloatField=CAST(REPLACE(TextField,',','.') AS FLOAT)

代替 , for .(如果有的话)然后转换为 FLOAT.希望这会有所帮助.

That replace the , for . (In case there is any) and then converts to FLOAT. Hope this help.

这篇关于SQL 将 varchar 列转换为浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！