How do I check if a SQL Server text column is empty?

Question

I am using SQL Server 2005. I have a table with a text column and I have many rows in the table where the value of this column is not null, but it is empty. Trying to compare against '' yields this response:

The data types text and varchar are incompatible in the not equal to operator.

Is there a special function to determine whether the value of a text column is not null but empty?

Answer 1

I would test against SUBSTRING(textColumn, 0, 1)

Answer 2

where datalength(mytextfield)=0

Answer 3

Actually, you just have to use the LIKE operator.

SELECT * FROM mytable WHERE mytextfield LIKE ''

Answer 4

Are null and an empty string equivalent? If they are, I would include logic in my application (or maybe a trigger if the app is "out-of-the-box"?) to force the field to be either null or '', but not the other. If you went with '', then you could set the column to NOT NULL as well. Just a data-cleanliness thing.

Answer 5

ISNULL( case textcolum1 WHEN '' THEN NULL ELSE textcolum1 END ,textcolum2) textcolum1