Join On Text Fields In Query

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.