How to convert Varchar to Int in sql server 2008

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

you can use convert function    Select convert int  Column1

User · Answer

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

User · Answer

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

User · Answer

Try the following code  In most case  it is caused by the comma issue   cast replace  FIELD NAME          as float

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

Examples related to sql

Examples related to sql-server-2005

Examples related to sql-server-2008