email belongs to table booking and its defined as type "Text" in our Microsoft sql server
SELECT email,
COUNT(email) AS NumOccurrences
FROM Booking
GROUP BY email
HAVING ( COUNT(email) > 1 )
after running the above query(trying to find duplicates emails in the booking) I got the error message like this:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I am using Microsoft Sql
since you are using SQL Server, why not change the data type to VARCHAR(100)?
To work around this error without changing the datatype, the TEXT or NTEXT column needs to be converted to VARCHAR or NVARCHAR when used in either the ORDER BY clause or the GROUP BY clause of a SELECT statement. eg, which is alittle bit messy
SELECT CAST(email AS NVARCHAR(100)) email,
COUNT(CAST(email AS NVARCHAR(100))) AS NumOccurrences
FROM Booking
GROUP BY CAST(email AS NVARCHAR(100))
HAVING COUNT(CAST(email AS NVARCHAR(100))) > 1
I had an issue with the accepted answer because of an issue with my ORM. This answer is only a workaround for when the accepted answer won't work, and is only valid if you only have the column in group by because it is required so you can include the column in the select.
It casts the column inside of aggregate function, so that it is not required in the group by, and the error will not occur and the returned data should still be correct.
MIN(CAST(email AS NVARCHAR(4000))) as email
Ideally you'd find a way to avoid this hack, but this may be useful in some circumstances.
SELECT email, name FROM users GROUP BY name, which is not allowed in SQL Server, select the email using SELECT MIN(CAST(email AS NVARCHAR(4000))) as email, name
textfor emails? "ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types" msdn.microsoft.com/en-us/library/ms187993.aspx You cannot group bytext: "Columns of type text, ntext, and image cannot be used in group_by_expression" msdn.microsoft.com/en-us/library/ms177673.aspx