Quote:
Originally Posted by Lord Humungus
SELECT TOP 10
currentmodel,comments
FROM items
WHERE comments is not null and comments <> ''
I get this error: [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
|
It's always best to describe the table schema(s) for the table(s) involved. In this case, it looks like "items" is declared as a text column, so normal string operations won't work.
There are a few ways to get around this. You can:
1. cast comments to varchar and then compare
2. use the datalength() function, which works on text
Also, because NULL values fail any operator, if you use one of the above conditions, then it should not be necessary to check for IS NOT NULL.
And finally, I find it's always helpful to fully qualify column names, especially when joining between tables. In this case, you are querying from only one table, so it's not that important.
Code:
SELECT TOP 10
items.currentmodel,
items.comments
FROM items
WHERE convert(varchar, comments) <> ''