SQL Server – ntext cannot be selected as DISTINCT
March 23, 2010 2 Comments
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