The Data Type Text Cannot Be Used as an Operand to The Union, Intersect or Except Operators Because It Is Not Comparable

The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable

Correct way

Stop using TEXT it is obsolete. Alter table schema.

ntext, text, and image data types will be removed in a future version
of Microsoft SQL Server. Avoid using these data types in new
development work, and plan to modify applications that currently use
them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Workaround

Cast to NVARCHAR(MAX):

SELECT TableA.Id,TableA.Owner, CAST(TableA.DescriptionText AS NVARCHAR(MAX))
FROM TableA
WHERE TableA.Owner=@User
UNION
SELECT TableA.Id,TableA.Owner, CAST(TableA.DescriptionText AS NVARCHAR(MAX))
FROM TableA LEFT JOIN TableB ON (TableA.Id=TableB.Id)
WHERE TableB.Participant = @User

Intersect with Geometry column in SQL Server

Finally, the only solution that works for my case is to create another table, for example, LocationGeo with a 1-1 relationship.

I do the Intersect or Union in the Location table with Linq and a Include of the new table.

SQL use select statement to add build a table - no insert op

With kudos to Marc Guillot for his answer, does this get your desired result?

declare @MetresPerMile float = 1609.344;

with Points as (
  select *
  from (values
    (1, GEOGRAPHY::Point(48.83000,-97.31000,4326)),
    (2, GEOGRAPHY::Point(22.9230000,-94.5342000,4326))
  ) pts (Id, Point)
)
select Start.Id as Start, Dest.Id as Dest, 
       Start.Point.STDistance(Dest.Point)/@MetresPerMile as Distance
from Points as Start
cross join Points as Dest

How to do UNION on two geometry tables?

There are two approaches,

1) Use UNION ALL instead of UNION ,

we know there is difference between them, but maybe it is okay with your need.

2) Use CAST function for converting geometry to varbinary(MAX)

as next:

Create table #myTable1 (ObjectTypeId int identity , GeomColumn geometry)
Create table #myTable2 (ObjectTypeId int identity , GeomColumn geometry)

INSERT INTO #myTable1 (GeomColumn)  
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));  

INSERT INTO #myTable2 (GeomColumn)  
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));  

select 1 AS ObjectTypeId , Cast( GeomColumn as varbinary(MAX)) geom 
from #myTable1
union 
select ObjectTypeId ,  Cast( GeomColumn as varbinary(MAX)) geom 
from #myTable2

Drop table #myTable1
Drop table #myTable2

Result: (one Record displayed via using UNION that avoid dublicated records)

intersect cast error

If you read MSDN They mention that,

The query specification or expression cannot return xml, text, ntext,
image, or nonbinary CLR user-defined type columns because these data
types are not comparable.

Use something like cast(textcolumnname as nvarchar(2000)) as textcolumnname instead of text field names.

I don't think there is any other way to do it if you want to use the INTERSECT you have to convert the datatype to the varchar.

The Other way is use the INNER JOIN with DISTINCT that will give you the same result as the INTERSECT.
Here is the detail description how to achive intersect with Inner join

TSQL: How to get a UNION result from two tables with XML information in T1.Field_A and nvarchar in T2.Field_A

If you look at your expected result you see, that XML and string would be mixed in the same column. This is not possible...

What you can do, is either use .value() instead of .query() to read the SourceEventId as string:

IncidentRequest_XML.value('(INCIDENT_REQUEST/SourceEventID/text())[1]','nvarchar(100)')  as SourceEventID

Or you can use a cast, to transform your result of .query() to a string:

CAST(IncidentRequest_XML.query('INCIDENT_REQUEST/SourceEventID') AS NVARCHAR(100)) as SourceEventID

Good to know: .query() returns a XML-typed result, which is the result of a XQuery-expression. On the other hand .value() can deal with a singleton value only (therefore we need the (blah)[1] and the specific type.

---

Related Topics