How to convert Varchar to Int in sql server 2008.
i have following code when i tried to run it wont allowed me to convert Varchar to 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 ?
This question is related to
sql
sql-server-2005
sql-server-2008
Spaces will not be a problem for cast
, however characters like TAB
, CR
or LF
will appear as spaces, will not be trimmed by LTRIM
or RTRIM
, and will be a problem.
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 REPLACE
to clean up your data.
Per your comment, you can use REPLACE
as part of your 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 CR
and LF
before it is entered.
Try the following code. In most case, it is caused by the comma issue.
cast(replace([FIELD NAME],',','') as float)
There are two type of convert method in 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.
Using Convert
Select convert(int,[Column1])
Using Cast
Select cast([Column1] as int)
you can use convert function :
Select convert(int,[Column1])
Try with below command, and it will ask all values to INT
select case when isnumeric(YourColumn + '.0e0') = 1 then cast(YourColumn as int) else NULL end /* case */ from YourTable
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 abc123
to an int.
UPDATE
Based on your comments I would remove any spaces that are in the values you are trying to convert.
That is the correct way to convert it to an INT as long as you don't have any alpha characters or NULL values.
If you have any NULL values, use
ISNULL(column1, 0)
Source: Stackoverflow.com