How to convert Nvarchar column to INT

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 <> '' 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 

5 Answers

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.

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 

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 

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 

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.