How to convert nvarchar column to INT

Question

I have a nvarchar column in one of my tables here. Now, I am in need of converting that column's values to the INT type.

I had tried using

cast(A.my_NvarcharColumn as INT) 

and

convert (int, N'A.my_NvarcharColumn')

When I had executed my query I am getting the following error:

Conversion failed when converting the nvarchar value ' 23454542 ' to data type int.

I am posting my entire code snippet below:

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

Answer 1

"CONVERT" considers the column name, not the string that has the column name; your current expression is attempting to convert the string A.my_NvarcharColumn to an integer instead of the column content.

SELECT convert (int, N'A.my_NvarcharColumn') FROM A;

Instead should be,

SELECT convert (int, A.my_NvarcharColumn) FROM A;

Want to be a SQL expert? Come and join this SQL Certification by Intellipaat.

You can check out the below SQL Tutorial video for better understanding.

Answer 2

Below are the steps of converting an nvarchar colum to INT in sql:

1.Using CAST():

SELECT CAST(your_nvarchar_column AS INT) AS your_int_column from your_table;

2.Using CONVERT():

               SELECT CONVERT(INT, your_nvarchar_column) AS your_int_column from your_table;

 

Example:

A table named employees with a column salary of type nvarchar that you want to convert to INT:

             SELECT CAST(marks AS INT) AS marks_int FROM studentmarks;

Note:

Ensure that all nvarchar values are numeric before conversion; use TRY_CAST() or TRY_CONVERT() for safer handling. Always back up your data before schema changes, and always validate convertible values with functions like ISNUMERIC(). If you are changing the column type permanently, then use ALTER TABLE after verifying the validity of the data.