问题描述
导入工具将多个表中的每一列作为 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 26Error 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
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 列转换为浮点数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!