[thelist] [SQL Server] Can you do a UNION on a text column?

Paul Cowan evolt at funkwit.com
Thu Sep 15 07:08:57 CDT 2005


On 15/09/2005 6:00 PM +1000 Mark Mandel wrote:
> I have a bad feeling the answer is going to be 'no' - but is there a
> way in which you can do a UNION on a SQL server field that is of type
> 'text'.

Well, you could convert the text to a var...

> I can't convert the text field to varchar, as the text is longer than
> 8000 characters.

... oh. Then no. You can't do a UNION.

BUT (if it suits) you CAN do a UNION ALL. This may or may not be 
appropriate, but if it is, it should work fine.

The difference (if you didn't know) is that a UNION ALL just piles all the 
data together, while a UNION effectively performs a DISTINCT on the result 
-- and you can't do DISTINCT across LOB (text, ntext, or image) fields, 
hence your problem. If your data doesn't contain duplicates, or you don't 
mind if duplicates come out, you'll be A-OK.

Cheers,

Paul Cowan


More information about the thelist mailing list