Text and NVARCHAR are incompatible

Question

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)

Answer 1

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);

Answer 2

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.

Answer 3

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)