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?

Kei

I would convert the data type if possible to varchar (max), text has been deprecated - best to start making the changes now if you are touching the table. Check with your dba of course. But the more things can get converted before they must be converted the better is my thought. It will depend on how much code you have using things like contains and write text which will be broken as to whether to do this now, but I bring it up, so you are aware that this will need to be changed eventually. – Dziggetai

Answer 1

where datalength(mytextfield)=0

Naldo

This was not the actual question, but just a remark for people that only read the title, don't forget to add OR mytextfield IS NULL when your column can be NULL – Klipspringer

mytextfield IS NULL *OR* :-) – Hoot

@Hoot SQL Server T-SQL doesn't honor short-circuit evaluation techniques, so order here doesn't effect the outcome. – Daily

Answer 2

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

Chemisorption

The above query will actually handle nullness and emptiness nature of a text column and accordingly assign value based on condition. Upvote for the answer since this is what I was looking for. Thanks – Frederiksen

Answer 3

Actually, you just have to use the LIKE operator.

SELECT * FROM mytable WHERE mytextfield LIKE ''

Cranach

+1 I prefer this answer over the others here because it does not rely on the added overhead of calling SQL Functions like DataLength(), IsNull(), or Cast(). Maybe the generated query plan is the same (I didn't check); still I find this to be a far cleaner approach. – Ossieossietzky

Answer 4

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.

Monoatomic

Don't you mean myColumn IS NOT NULL AND my column = '';? – Quarterhour

Answer 5

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

Norine

upvoted, but ... where ISNULL( TRIM( Field), '') = '' is even better ;-), if you feel that " " is an Empty string even with spaces inside – Plywood

For MySQL the correct syntax is: SQL SELECT * FROM TABLE WHERE IFNULL(FIELD, '')='' – Snowslide

Answer 6

Use the IS NULL operator:

Select * from tb_Employee where ename is null

Tineid

atoumey states in the question that "the value of this column is not null, but it is empty" therefore ISNULL() wouldn't work :) – Peripteral

Answer 7

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)

Colonial

Answer 8

Use DATALENGTH method, for example:

SELECT length = DATALENGTH(myField)
FROM myTABLE

Selfcommand

Answer 9

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

Norine

you first check if is null, then you use len function... i hope it helps – Norine

What if the Column is not NULL and Empty, having no Text? – Cordelia

spaces? or what do you mean? – Norine

Answer 10

DECLARE @temp as nvarchar(20)

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

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

Habitforming

You can also use IIF(DATALENGTH(@temp=0,'[Empty]',@temp) – Tisdale

Answer 11

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

Cantharides

Answer 12

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.

Angle

Answer 13

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

Introjection

Wow, a subquery. That's not expensive at all. – Quagmire

Answer 14

You have to do both:

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

Bamboozle

Answer 15

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], ''))))

Dissect

Answer 16

try this:

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

i hope help u!

Aquacade

Answer 17

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;

Cuirass