SQL Server – How to Join on Text Fields in Query

join;sql serversql-server-2008-r2t-sql

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

Best Answer

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

Related Question