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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With