2

I am unable to change DDL for the tables, but I need to join on two text fields. I get an error of

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

Below is sample DDL that illustrates my issue, how do I overcome?

Declare @Table1 Table(id text,name nvarchar(200))
Insert Into @Table1 (id, name) Values
('123', 'Joe'), ('222.00', 'Jim')
Declare @Table2 Table (id text,green nvarchar(200))
Insert Into @Table2 (id, green) Values
('123', 'JDSKLJS'), ('222.00', 'KLJKLJJKL')
Select
*
FROM @Table1 t1
INNER JOIN @Table2 t2
ON t1.id = t2.id

3 Answers 3

4

You can cast text fields as varchar(max)

Declare @Table1 Table(id text,name nvarchar(200))
Insert Into @Table1 (id, name) Values
('123', 'Joe'), ('222.00', 'Jim')
Declare @Table2 Table (id text,green nvarchar(200))
Insert Into @Table2 (id, green) Values
('123', 'JDSKLJS'), ('222.00', 'KLJKLJJKL');

Select
*
FROM @Table1 t1
INNER JOIN @Table2 t2
ON cast(t1.id as varchar(max)) = cast(t2.id as varchar(max))
GO
id     | name | id     | green    
:----- | :--- | :----- | :--------
123    | Joe  | 123    | JDSKLJS  
222.00 | Jim  | 222.00 | KLJKLJJKL

dbfiddle here

1

You'll have to convert (in the join) to a smaller data type:

Select *
FROM @Table1 t1
INNER JOIN @Table2 t2
ON CONVERT(varchar(8000),t1.id) = CONVERT(varchar(8000),t2.id)

Or some smaller number that you know will make the match unique.

0
SELECT *
FROM @Table1 t1
INNER JOIN @Table2 t2
ON CAST(t1.id AS varchar(max)) = CAST(t2.id AS varchar(max))

Note that the performance will be pretty awful, so I wouldn't do this in any sort of production code; refactor the schema to better support what you're trying to do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.