Conversion failed when converting the varchar value to data type int in a JOIN

Question

So we have a SQL Server Stored Procedure that has a JOIN using a data column that is defined in the "To Be" Joined Table as INT. The data column in our joining criteria is defined as a data type NVARCHAR(100). So the SQL Server Stored Procedure is failing with "Conversion failed when converting the varchar value to data type int."

How can I get around this within the JOIN criteria and handle if the joining data column is NOT Numeric?

Thanks for your review and am hopeful for a reply.

Answer 1

Assuming your first table is named A with a column named MyIntColumn of type int and your second table is named B with a column named MyNVarcharColumn of type nvarchar, you could do

From A
Inner Join B On A,MyIntColumn = Try_Cast(B.MyNVarcharColumn As int) 

Tom

Answer 2

You can use TRY_CAST() or TRY_CONVERT() to convert the varchar value to data type int. If the conversion fails, it returns NULL.

Answer 3

Hi @Bobby P ,
You can try to convert the int data column to nvarchar type through the cast or convert function.(CAST and CONVERT (Transact-SQL))

I wrote an example for testing, please refer to：

CREATE TABLE #tblTest  
(FileData varchar(MAX),  
num nvarchar(25))  
INSERT INTO #tblTest values ('TEST1;Testing 123;',1)  
INSERT INTO #tblTest values ('TEST2;Testing.234;',2)  
INSERT INTO #tblTest values ('TEST3;Testing 345;',3)  
INSERT INTO #tblTest values ('TEST4;Testing 456;',4)  
INSERT INTO #tblTest values ('TEST5;Testing5.67;',5)  
  
create table test  
(id int)  
insert into test values('1'),('2'),('5')     
      
select * from #tblTest a  
join test b  
on a.num=cast(b.id as nvarchar(25))  
  
drop table #tblTest  
drop table test  

I have solved similar problems on msdn, you can refer to：
[Conversion failed when converting the varchar value '],' to data type int.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo