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?

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. — HLGEM, Jun 23 '10 at 18:35

score 341 · Accepted Answer · edited Apr 17 '12 at 14:54

341

where datalength(mytextfield)=0

edited Apr 17 '12 at 14:54

answered Aug 28 '08 at 21:14

Eric Z Beard

37,669
27
100
145

4

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` – Daan Aug 27 '15 at 09:50
5

`mytextfield IS NULL *OR*` :-) – ban-geoengineering Nov 20 '16 at 20:24
4

@ban-geoengineering SQL Server T-SQL doesn't honor short-circuit evaluation techniques, so order here doesn't effect the outcome. – Conrad Aug 03 '17 at 18:01

score 54 · Answer 2 · edited Feb 03 '12 at 18:08

54

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

edited Feb 03 '12 at 18:08

user_v

9,628
4
40
32

answered Jun 23 '10 at 18:29

Eric

541
4
2

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 – user_v Feb 03 '12 at 17:10

score 36 · Answer 3 · answered Aug 29 '08 at 06:06

36

Actually, you just have to use the LIKE operator.

SELECT * FROM mytable WHERE mytextfield LIKE ''

answered Aug 29 '08 at 06:06

Dane

9,737
5
28
23

1

+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. – MikeTeeVee Nov 13 '19 at 11:49

Nima · Answer 4 · 2015-05-29T06:28:35.117

15

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.

edited May 29 '15 at 06:28

answered Aug 22 '14 at 20:00

Nima

314
2
7

Don't you mean `myColumn IS NOT NULL AND my column = '';`? – bcsb1001 Aug 22 '14 at 20:13

Enrique Garcia · Answer 5 · 2018-05-08T22:39:53.733

3

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

edited May 08 '18 at 22:39

answered Feb 10 '14 at 19:21

Enrique Garcia

79
4

1

upvoted, but ... where ISNULL( TRIM( Field), '') = '' is even better ;-), if you feel that " " is an Empty string even with spaces inside – Kirsten Jun 13 '19 at 07:26
For MySQL the correct syntax is: ```SQL SELECT * FROM TABLE WHERE IFNULL(FIELD, '')='' ``` – Cláudio Silva Jan 27 '20 at 10:40

score 2 · Answer 6 · answered Jun 04 '13 at 10:49

2

Use the IS NULL operator:

Select * from tb_Employee where ename is null

answered Jun 04 '13 at 10:49

Pearl

8,373
8
40
59

1

atoumey states in the question that "the value of this column is not null, but it is empty" therefore ISNULL() wouldn't work :) – GazB Sep 20 '13 at 15:32

score 1 · Answer 7 · edited Jun 14 '12 at 07:49

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)

edited Jun 14 '12 at 07:49

sloth

99,095
21
171
219

answered Jun 13 '12 at 15:05

Mike Roberts

19
1

score 1 · Answer 8 · answered Aug 27 '15 at 20:15

1

Use DATALENGTH method, for example:

SELECT length = DATALENGTH(myField)
FROM myTABLE

answered Aug 27 '15 at 20:15

Jorgesys

124,308
23
334
268

score 1 · Answer 9 · edited Jun 14 '19 at 13:52

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

edited Jun 14 '19 at 13:52

barbsan

3,418
11
21
28

answered Jun 14 '19 at 13:28

Enrique Garcia

79
4

you first check if is null, then you use len function... i hope it helps – Enrique Garcia Jun 14 '19 at 13:30
What if the Column is not NULL and Empty, having no Text? – Sayed Muhammad Idrees Jan 31 '20 at 17:43
spaces? or what do you mean? – Enrique Garcia Sep 28 '20 at 17:21

score 0 · Answer 10 · answered Sep 07 '12 at 23:11

0

You have to do both:

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

answered Sep 07 '12 at 23:11

marklark

860
1
8
18

score 0 · Answer 11 · answered Aug 07 '15 at 10:09

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

score 0 · Answer 12 · answered Aug 28 '08 at 21:11

0

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

answered Aug 28 '08 at 21:11

bdukes

152,002
23
148
175

score 0 · Answer 13 · answered Aug 29 '08 at 20:03

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.

score 0 · Answer 14 · answered Oct 10 '18 at 12:47

0

try this:

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

i hope help u!

answered Oct 10 '18 at 12:47

Luiz Fernando Corrêa Leite

842
1
7
11

score 0 · Answer 15 · answered Aug 23 '19 at 14:15

0

DECLARE @temp as nvarchar(20)

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

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

answered Aug 23 '19 at 14:15

user11968038

1

score 0 · Answer 16 · answered Jul 29 '21 at 09:26

0

It will do two things:

Null check and string null check

Replace empty value to default value eg NA.

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

answered Jul 29 '21 at 09:26

Pallav Chanana

617
5
10

score 0 · Answer 17 · answered Mar 07 '12 at 19:50

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 Mar 07 '12 at 19:50

Yoosaf Abdulla

3,722
4
31
34

Wow, a subquery. That's not expensive at all. – Jay Croghan Nov 26 '18 at 11:28

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

17 Answers17

Linked