How do I check if a SQL Server text column is empt

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 know this post is ancient but, I found it useful.

It didn't resolve my issue of returning the record with a non empty text field so I thought I would add my solution.

This is the where clause that worked for me.

WHERE xyz LIKE CAST('% %' as text)

Answer 2

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

Answer 3

You could do like

SELECT * FROM TABLE WHERE FIELDNAME=''

Answer 4

Use the IS NULL operator:

Select * from tb_Employee where ename is null

Answer 5

Actually, you just have to use the LIKE operator.

SELECT * FROM mytable WHERE mytextfield LIKE ''

Answer 6

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