Back

Explore Courses Blog Tutorials Interview Questions
0 votes
3 views
in SQL by (6.1k points)

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

1 Answer

0 votes
by (12.7k points)
edited by

"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.

Related questions

0 votes
1 answer
asked Jul 4, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.5k questions

29.9k answers

500 comments

99.1k users

Browse Categories

...