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

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?

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


[1]:

[To see links please register here]

Actually, you just have to use the LIKE operator.

SELECT * FROM mytable WHERE mytextfield LIKE ''

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.

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

I wanted to have a predefined text("No Labs Available") to be displayed if the value was null or empty and my friend helped me with this:

StrengthInfo = CASE WHEN ((SELECT COUNT(UnitsOrdered) FROM [Data_Sub_orders].[dbo].[Snappy_Orders_Sub] WHERE IdPatient = @PatientId and IdDrugService = 226)> 0)
THEN cast((S.UnitsOrdered) as varchar(50))
ELSE 'No Labs Available'
END

where datalength(mytextfield)=0

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)

You have to do both:

`SELECT * FROM Table WHERE Text IS NULL or Text LIKE ''`

Use the IS NULL operator:

Select * from tb_Employee where ename is null