[sql] How to convert Varchar to Int in sql server 2008?

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

The answer is


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)

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?