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)

Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

3 answers

Sort by: Most helpful
  1. Dan Guzman 9,406 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);
    
    1 person found this answer helpful.
    0 comments No comments

  2. MelissaMa-msft 24,221 Reputation points Moderator
    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.

    0 comments No comments

  3. Bruce (SqlWork.com) 78,236 Reputation points Volunteer Moderator
    2025-06-06T16:29:53.77+00:00

    text, ntext and binary are old datatypes, that have limited support. you can not directly compare a text or ntext column to anything, not even another text column. you either extract a segment with substring or convert to another datatype before you can do a compare.

    as text is a variable character array supporting up to 2,147,483,647 bytes. you should replace this columns with varchar(max). replace ntext with nvarchar(max)

    to fix the query, just do a cast

    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 varchar(max)) = substring(b.dept_code,3,6)
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.