Distinct function error in sql server

Question

Hello All,

I have a query and I need to get distinct of a field. Datatype is varchar.
But Its throwing below error.
I tried with cast but still the same error.

Code : distinct (convert(nvarchar(max),id))

Error :

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

Could anyone please help

Answer 1

If the data type of the column is ntext, you need to convert it to nvarchar and then you are able to use DISTINCT. If the field id is ntext, your code should work

SELECT DISTINCT(CONVERT(nvarchar(max), id))
FROM YOUR_TABLE

Or you can use CAST:

SELECT DISTINCT(CAST(id AS nvarchar(max)))
FROM YOUR_TABLE

Answer 2

Hi @vissupangam-0394,

The problem is caused by the fact that SQL Server’s ntext, text, and image data types have been declared obsolete.
You can try to use the following code:

select distinct (CAST (id AS nvarchar(max))) from table_name  

the permanent and suggested solution is to permanently change the data type of the column(s) using the obsolete data type(s) into their corresponding accepted data types:

ntext should become nvarchar(max)  
text should become varchar(max)  
image should become varbinary(max)  

it can avoid losing valuable time with multiple CASTs and keep your query safe from performance issues.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15