question

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

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.