SQL Server – ntext cannot be selected as DISTINCT

I needed to make a distinct result set from a table that contained ntext, and I got the error message below;

The ntext data type cannot be selected as DISTINCT because it is not comparable.

The query (this is from Northwind) looks like this (I know, here I don’t need the distinct, but this is an example);

select distinct * from Suppliers

What to do? The solution is to cast the ntext fields to some other field type that we can perform distinct against, for instance varchar(1000). This means we loose text in the result set – if there is more text than 1000 characters in the column we’re casting. But that’s the price we’ll have to pay;

select distinct 
  SupplierID, 
  CompanyName, 
  ContactName, 
  ContactTitle, 
  Address, 
  City, 
  Region, 
  PostalCode, 
  Country, 
  Phone, 
  Fax, 
  cast(HomePage as varchar(50)) 
from 
  Suppliers

Now, doing this manually is rather dull, so I created this script to automate it. To find out which of your fields are ntext, you can use this view;

select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'Suppliers'

select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'Suppliers' and DATA_TYPE='ntext'

The SQL Statement below will generate a SQL statement that casts ntext fields;

declare @TableName varchar(90)

-- Replace this with your table name
set @TableName = 'Suppliers'

declare 
  @sql varchar(MAX)

set @sql = 'select distinct ';  
  
select 
  @sql = @sql + 
  case 
    when DATA_TYPE='ntext' then 'cast('+COLUMN_NAME+' as varchar(50)),'
    else COLUMN_NAME + ',' 
   end
from 
  INFORMATION_SCHEMA.COLUMNS 
where 
  TABLE_NAME = @TableName

set @sql = substring(@sql, 1, len(@sql)-1)

set @sql = @sql + ' from ' + @TableName

print @sql

About mfagerlund
Writes code in my sleep - and sometimes it even compiles!

2 Responses to SQL Server – ntext cannot be selected as DISTINCT

  1. Mavi07 says:

    Solution: Cast NTEXT to NVARCHAR(MAX) so that it can hold the data (will not lose the data) and then apply DISTINCT.

  2. Chris Grisham says:

    Thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: