question

Bone12-2270 avatar image
0 Votes"
Bone12-2270 asked MelissaMa-msft commented

Text and NVARCHAR are incompatible

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Bone12-2270

Could you please validate the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
DanGuzman avatar image
1 Vote"
DanGuzman answered

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);
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Bone12-2270

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.