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

191

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?

This question is tagged with sql-server sql-server-2005 sqldatatypes notnull emptydatatext

~ Asked on 2008-08-28 21:09:00

16 Answers


315

where datalength(mytextfield)=0

~ Answered on 2008-08-28 21:14:17


49

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

~ Answered on 2010-06-23 18:29:42


29

Actually, you just have to use the LIKE operator.

SELECT * FROM mytable WHERE mytextfield LIKE ''

~ Answered on 2008-08-29 06:06:25


7

To get only empty values (and not null values):

SELECT * FROM myTable WHERE myColumn = ''

To get both null and empty values:

SELECT * FROM myTable WHERE myColumn IS NULL OR myColumn = ''

To get only null values:

SELECT * FROM myTable WHERE myColumn IS NULL

To get values other than null and empty:

SELECT * FROM myTable WHERE myColumn <> ''


And remember use LIKE phrases only when necessary because they will degrade performance compared to other types of searches.

~ Answered on 2014-08-22 20:00:34


3

SELECT * FROM TABLE
WHERE ISNULL(FIELD, '')=''

~ Answered on 2014-02-10 19:21:43


2

Use the IS NULL operator:

Select * from tb_Employee where ename is null

~ Answered on 2013-06-04 10:49:48


1

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

~ Answered on 2019-06-14 13:28:39


1

Use DATALENGTH method, for example:

SELECT length = DATALENGTH(myField)
FROM myTABLE

~ Answered on 2015-08-27 20:15:21


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)

~ Answered on 2012-06-13 15:05:47


0

DECLARE @temp as nvarchar(20)

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

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

~ Answered on 2019-08-23 14:15:14


0

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

~ Answered on 2008-08-28 21:11:00


0

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.

~ Answered on 2008-08-29 20:03:04


0

I know there are plenty answers with alternatives to this problem, but I just would like to put together what I found as the best solution by @Eric Z Beard & @Tim Cooper with @Enrique Garcia & @Uli Köhler.

If needed to deal with the fact that space-only could be the same as empty in your use-case scenario, because the query below will return 1, not 0.

SELECT datalength(' ')

Therefore, I would go for something like:

SELECT datalength(RTRIM(LTRIM(ISNULL([TextColumn], ''))))

~ Answered on 2015-08-07 10:09:12


0

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

~ Answered on 2012-03-07 19:50:03


0

You have to do both:

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

~ Answered on 2012-09-07 23:11:46


0

try this:

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

i hope help u!

~ Answered on 2018-10-10 12:47:16


Most Viewed Questions: