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

Bobby P 221 Reputation points
2020-08-29T13:45:37.25+00:00

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.

{count} votes

3 answers

Sort by: Most helpful
  1. Tom Cooper 8,026 Reputation points
    2020-08-29T16:37:09.963+00:00

    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

    No comments

  2. Guoxiong 7,681 Reputation points
    2020-08-29T21:21:58.44+00:00

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

    No comments

  3. EchoLiu-MSFT 14,416 Reputation points
    2020-08-31T02:49:06.093+00:00

    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  
    

    21446-image.png

    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

    No comments