CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user998609
user998609

Reputation: 21

Is it possible to join on columns with datatype Text or ntext?

Is it possible to join on columns with datatype Text or ntext?

Upvotes: 0

Views: 2186

Answers (1)

Igor Borisenko
Igor Borisenko

Reputation: 3866

NO (without an explicit conversion).

From BOL

Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types.

This code

create table #t1 (t text)
create table #t2 (t text)

insert into #t1 values ('1')
insert into #t1 values ('2')
insert into #t1 values ('3')

insert into #t2 values ('1')
insert into #t2 values ('2')

select *
from #t1 t1 
     join #t2 t2 ON t1.t=t2.t

----this works    
    --select *
    --from #t1 t1 
    --   join #t2 t2 ON cast(t1.t as varchar(max))=cast(t2.t as varchar(max))

drop table #t1
drop table #t2

gives the error:

The data types text and text are incompatible in the equal to operator.

Upvotes: 2

Related Questions