Text and NVARCHAR are incompatible

Bone_12 361 Reputation points
2021-11-03T09:36:37.253+00:00

Hi,

I am trying to join 2 tables together but once column is a text, and the other nvarchar and as a result, I get the following error message

Msg 402, Level 16, State 1, Line 101
The data types text and nvarchar are incompatible in the equal to operator.

Any idea how I can get around this please?

This is my code:

select
a.cust_no,
a.dept_issue,
a.dept_code as dept_code,
b.name as dept_name

from [table].[dbo].[Dept] as a
left join [table].[dbo].[MTP] as b
on a.dept_code = substring(b.dept_code,3,6)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,578 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Dan Guzman 6,931 Reputation points
    2021-11-03T10:19:07.437+00:00

    The error indicates the Dept table dept_code column is the deprecated text data type. This is most likely database design flaw since you probably don't need 2 billion characters for a dept_code value.

    It would be best to fix the column type to match the MTD table dept_code column type but a temporary work-around is a substring of the value in the query:

    select
        a.cust_no,
        a.dept_issue,
        a.dept_code as dept_code,
        b.name as dept_name
    from [dbo].[Dept] as a
    left join [dbo].[MTP] as b
    on SUBSTRING(a.dept_code, 1, 6) = substring(b.dept_code,3,6);
    
    No comments

  2. MelissaMa-MSFT 24,126 Reputation points Microsoft Employee
    2021-11-04T01:48:21.7+00:00

    Hi @Bone_12

    We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data.

    I made some tests from my side and found that you would face that error only when the data type of dept_code from [Dept] table was text while it was nvarchar in [MTP] table.

    Then you could update your query like below:

    select  
    a.cust_no,  
    a.dept_issue,  
    a.dept_code as dept_code,  
    b.name as dept_name  
    from [table].[dbo].[Dept] as a  
    left join [table].[dbo].[MTP] as b  
    on cast(a.dept_code as nvarchar(10)) = substring(b.dept_code,3,6)  
    

    Best regards,
    Melissa


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments