Comparing Text DataType???

I'm trying to do a simple search for any record that has an empty 'dsc' field. There isn't a NULL character in there, just a SPACE character. The 'dsc' field is a description field so it's datatype is TEXT.

Code:


SELECT id FROM leads WHERE dsc<>''


Normally I can do a VARCHAR(50) like that and get the results I'm looking for. When I do this on a TEXT datatype I get this error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I I tried a couple of things, but it's not getting me the correct results.

Code:


SELECT id FROM leads WHERE dsc NOT LIKE '% %'
SELECT id FROM leads WHERE dsc NOT LIKE '% '
SELECT id FROM leads WHERE dsc NOT LIKE ' %'


Please shed some light on this.. thanks!

Edited @ 12:08 PM
After some research I found an unconventional way of beating the system. If you have a more conventional way I would still like to see it. A quick fix is using DATALENGTH() function to compare the data. Basically if there are any characters besides NULL and space then the data length will be more than zero.

Code:


SELECT id FROM leads WHERE DATALENGTH(dsc)=0


http://msdn.microsoft.com/library/d..._da-db_4ep4.asp