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

where datalength(mytextfield)=0

Actually, you just have to use the LIKE operator.

SELECT * FROM mytable WHERE mytextfield LIKE ''

Instead of using isnull use a case, because of performance it is better the case.

case when campo is null then '' else campo end

In your issue you need to do this:

case when campo is null then '' else
  case when len(campo) = 0 then '' else campo en
end

Code like this:

create table #tabla(
id int,
campo varchar(10)
)

insert into #tabla
values(1,null)

insert into #tabla
values(2,'')

insert into #tabla
values(3,null)

insert into #tabla
values(4,'dato4')

insert into #tabla
values(5,'dato5')

select id, case when campo is null then 'DATA NULL' else
  case when len(campo) = 0 then 'DATA EMPTY' else campo end
end
from #tabla

drop table #tabla

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

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.

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

try this:

select * from mytable where convert(varchar, mycolumn) = ''

i hope help u!

DECLARE @temp as nvarchar(20)

SET @temp = NULL
--SET @temp = ''
--SET @temp = 'Test'

SELECT IIF(ISNULL(@temp,'')='','[Empty]',@temp)

It will do two things:

1. Null check and string null check
2. Replace empty value to default value eg NA.

SELECT coalesce(NULLIF(column_name,''),'NA') as 'desired_name') from table;