How to convert Nvarchar column to INT

Question

I have a nvarchar column in one of my tables  Now I need to convert that column values to INT type      I have tried using     cast A my NvarcharColumn as INT     and    convert  int  N A my NvarcharColumn     When I ran my query I am getting errors like        Conversion failed when converting the nvarchar value   23454542   to   data type int      hi i am posting my entire code snippet    SELECT A objID  name  des   right Replace Ltrim Replace substring my nvarcharcolumn 1 9    0                0   10  AS new nvarcharcolumn  INTO  tmp1 FROM  database   dbo   tblname  AS A INNER JOIN  SELECT   FROM  database   dbo  tblname1 WHERE sourceID   32  AS AI ON source   A objID INNER JOIN  database   dbo  tblname2 AS I ON I ObjectID   A Source  SELECT MAX m dAddDate  AS date Asof  dnum INTO  tmp2 FROM   SELECT   FROM  database   dbo  tblname WHERE senior  lt  gt     AND class    SSS   AS A GROUP BY dnum  SELECT DISTINCT A   INTO  tmp3 FROM  tmp1 AS A INNER JOIN  tmp2 AS SD ON SD dnum  cast A new nvarcharcolumn as INT  INNER JOIN database  dbo  tbl4 AS M ON M dnum   cast A new nvarcharcolumn as INT   AND SD date Asof   M adddate

User · Answer

Your CAST   looks correct   Your CONVERT   is not correct   You are quoting the column as a string   You will want something like  CONVERT INT  A my NvarcharColumn       notice without the quotes     The only other reason why this could fail is if you have a non-numeric character in the field value or if it s out of range   You can try something like the following to verify it s numeric and return a NULL if it s not   SELECT  CASE   WHEN ISNUMERIC A my NvarcharColumn    1 THEN CONVERT INT  A my NvarcharColumn    ELSE NULL  END AS my NvarcharColumn

User · Answer

If you want to convert from char to int  why not think about unicode number   SELECT UNICODE      -- 59   This way you can convert any char to int without any error  Cheers

User · Answer

CONVERT takes the column name  not a string containing the column name  your current expression tries to convert the string A my NvarcharColumn to an integer instead of the column content   SELECT convert  int  N A my NvarcharColumn   FROM A    should instead be  SELECT convert  int  A my NvarcharColumn  FROM A    Simple SQLfiddle here

User · Answer

You can always use the ISNUMERIC helper function to convert only what s really numeric   SELECT      CAST A my NvarcharColumn AS BIGINT  FROM       A WHERE      ISNUMERIC A my NvarcharColumn    1

User · Answer

I know its Too late But I hope it will work new comers Try This Its Working      D  select    case        when isnumeric my NvarcharColumn    1 then                cast my NvarcharColumn AS int        else               NULL  end  AS  my NvarcharColumnmitter  from A

[sql-server] How to convert Nvarchar column to INT

Examples related to sql-server

Examples related to sqldatatypes

Examples related to type-conversion