将带有数字的列的类型从 varchar 更改为 int [英] Change type of a column with numbers from varchar to int

问题描述

我们在当前类型为 varchar(16) 的数据库中有两列.事实是，它包含数字并且总是包含数字.因此，我们希望将其类型更改为整数.但问题是它当然已经包含数据.

We have two columns in a database which is currently of type varchar(16). Thing is, it contains numbers and always will contain numbers. We therefore want to change its type to integer. But the problem is that it of course already contains data.

有什么方法可以将列的类型从 varchar 更改为 int，并且不会丢失所有已经存在的数字?希望我们可以运行某种 sql，而不必创建临时列和创建 C# 程序或进行转换等等的东西......我想如果 SQL Server 有一些将字符串转换为数字，但我对 SQL 非常 不稳定.几乎只能使用 C# 并通过 LINQ to SQL 访问数据库.

Is there any way we can change the type of that column from varchar to int, and not lose all those numbers that are already in there? Hopefully some sort of sql we can just run, without having to create temporary columns and create a C# program or something to do the conversion and so forth... I imagine it could be pretty easy if SQL Server have some function for converting strings to numbers, but I am very unstable on SQL. Pretty much only work with C# and access the database through LINQ to SQL.

注意:是的，首先将列设为 varchar 并不是一个好主意，但不幸的是，他们就是这样做的.

Note: Yes, making the column a varchar in the first place was not a very good idea, but that is unfortunately the way they did it.

推荐答案

唯一可靠的方法是使用临时表，但 SQL 不会太多:

The only reliable way to do this will be using a temporary table, but it will not be much SQL:

select * into #tmp from bad_table
truncate table bad_table
alter bad_table alter column silly_column int
insert bad_table
select cast(silly_column as int), other_columns
from #tmp
drop table #tmp

这篇关于将带有数字的列的类型从 varchar 更改为 int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！