Join On Text Fields In Query 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
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