如何在 sql server 2008 中将 Varchar 转换为 Int？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7973224/
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
How to convert Varchar to Int in sql server 2008?
提问by Vikrant More
How to convert Varchar to Int in sql server 2008.
如何在 sql server 2008 中将 Varchar 转换为 Int。
i have following code when i tried to run it wont allowed me to convert Varchar to Int.
当我尝试运行时，我有以下代码，它不允许我将 Varchar 转换为 Int。
Select Cast([Column1] as INT)
Column1 is of Varchar(21) NOT NULL type and i wanted to convert it into Int. actually i am trying to insert Column1 into another table having Field as INT. can someone please help me to convert this ?
Column1 是 Varchar(21) NOT NULL 类型，我想将它转换为 Int。实际上，我正在尝试将 Column1 插入另一个字段为 INT 的表中。有人可以帮我转换这个吗？
回答by Jeff Ogata
Spaces will not be a problem for cast, however characters like TAB, CRor LFwill appear as spaces, will not be trimmed by LTRIMor RTRIM, and will be a problem.
空格对 来说不是问题cast，但是像TAB, CRor 之类的字符LF将显示为空格，不会被LTRIM或修剪RTRIM，并且会出现问题。
For example try the following:
例如尝试以下操作：
declare @v1 varchar(21) = '66',
@v2 varchar(21) = ' 66 ',
@v3 varchar(21) = '66' + char(13) + char(10),
@v4 varchar(21) = char(9) + '66'
select cast(@v1 as int) -- ok
select cast(@v2 as int) -- ok
select cast(@v3 as int) -- error
select cast(@v4 as int) -- error
Check your input for these characters and if you find them, use REPLACEto clean up your data.
检查您输入的这些字符，如果找到它们，请用于REPLACE清理您的数据。
Per your comment, you can use REPLACEas part of your cast:
根据您的评论，您可以将其REPLACE用作您的一部分cast：
select cast(replace(replace(@v3, char(13), ''), char(10), '') as int)
If this is something that will be happening often, it would be better to clean up the data and modify the way the table is populated to remove the CRand LFbefore it is entered.
如果这是经常发生的事情，最好在输入之前清理数据并修改表的填充方式以删除CR和LF。
回答by Ali Rasouli
you can use convert function :
您可以使用转换功能：
Select convert(int,[Column1])
回答by Abe Miessler
That is how you would do it, is it throwing an error? Are you sure the value you are trying to convert is convertible? For obvious reasons you cannot convert abc123to an int.
这就是你会怎么做，它会抛出错误吗？您确定要转换的值是可转换的吗？出于显而易见的原因，您无法转换abc123为 int。
UPDATE
更新
Based on your comments I would remove any spaces that are in the values you are trying to convert.
根据您的评论，我将删除您尝试转换的值中的任何空格。
回答by Eric
That is the correct way to convert it to an INT as long as you don't have any alpha characters or NULL values.
只要您没有任何字母字符或 NULL 值，这就是将其转换为 INT 的正确方法。
If you have any NULL values, use
如果您有任何 NULL 值，请使用
ISNULL(column1, 0)
回答by Jfang
Try the following code. In most case, it is caused by the comma issue.
试试下面的代码。在大多数情况下，它是由逗号问题引起的。
cast(replace([FIELD NAME],',','') as float)
回答by Shantanu
Try with below command, and it will ask all values to INT
尝试使用以下命令，它会向 INT 询问所有值
select case when isnumeric(YourColumn + '.0e0') = 1 then cast(YourColumn as int) else NULL end /* case */ from YourTable
select case when isnumeric(YourColumn + '.0e0') = 1 then cast(YourColumn as int) else NULL end /* case */ from YourTable
回答by Manish Vadher
There are two type of convert method in SQL.
SQL 中有两种类型的转换方法。
CAST and CONVERT have similar functionality. CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers. CAST is the more ANSI-standard of the two functions.
CAST 和 CONVERT 具有相似的功能。CONVERT 特定于 SQL Server，并在日期和时间值、小数和货币符号之间进行转换时提供更大的灵活性。CAST 是这两个函数中更符合 ANSI 标准的。
Using Convert
使用转换
Select convert(int,[Column1])
Using Cast
使用演员表
Select cast([Column1] as int)
