How do I check if a Sql server string is null or empty

Question

I want to check for data, but ignore it if it's null or empty. Currently the query is as follows...

Select              
Coalesce(listing.OfferText, company.OfferText, '') As Offer_Text,         
from tbl_directorylisting listing  
 Inner Join tbl_companymaster company            
  On listing.company_id= company.company_id

But I want to get company.OfferText if listing.Offertext is an empty string, as well as if it's null.

What's the best performing solution?

Martin Ba · Accepted Answer · 2015-03-27T10:32:37.997

487

I think this:

SELECT 
  ISNULL(NULLIF(listing.Offer_Text, ''), company.Offer_Text) AS Offer_Text
FROM ...

is the most elegant solution.

And to break it down a bit in pseudo code:

// a) NULLIF:
if (listing.Offer_Text == '')
  temp := null;
else
  temp := listing.Offer_Text; // may now be null or non-null, but not ''
// b) ISNULL:
if (temp is null)
  result := true;
else
  result := false;

edited Mar 27 '15 at 10:32

answered Jul 13 '10 at 08:19

Martin Ba

37,187
33
183
337

1

I couldn't decide if I should upvote your answer or unclefofa's, since he seems to have answered first, but his answer has been edited after you answered. I ended upvoting both. – Zecc Mar 22 '11 at 10:04
if listing.Offer_Text = ' ', it passes the NULLIF condition. I am sadfused. – Merritt Nov 09 '12 at 23:16
As long as company.Offer_Text isn't null but then this will complicate things... =) – Paul C Jan 03 '13 at 12:38
3

Shouldn't we use trim to make sure everything goes as planned – irfandar Aug 26 '13 at 11:00
5

@irfandar - Well, if you want to treat a string with all spaces as empty, go ahead use trim. Otherwise a string with all spaces is not empty. – Martin Ba Sep 02 '13 at 12:13
This is also a good way to emmulate `IIF(ISITNULL??, yes_value, no_value)` (It is weird to use `ISNULL` as the first argument due to it wanting two arguments of its own). Instead you can do `ISNULL(NULLIF(no_value, Null), yes_value)` – johnDanger Nov 15 '19 at 23:33

Joel Coehoorn · Answer 2 · 2015-03-18T21:45:22.247

67

SELECT
   CASE WHEN LEN(listing.OfferText) > 0 THEN listing.OfferText 
        ELSE COALESCE(Company.OfferText, '') END 
   AS Offer_Text,

...

In this example, if listing.OfferText is NULL, the LEN() function should also return NULL, but that's still not > 0.

Update

I've learned some things in the 5 1/2 years since posting this, and do it much differently now:

COALESCE(NULLIF(listing.OfferText,''), Company.OfferText, '')

This is similar to the accepted answer, but it also has a fallback in case Company.OfferText is also null. None of the other current answers using NULLIF() also do this.

edited Mar 18 '15 at 21:45

answered Dec 02 '08 at 14:34

Joel Coehoorn

399,467
113
570
794

1

This got upvoted today, so it's indexed somewhere. I now know a better way to do this than the original answer. – Joel Coehoorn Mar 18 '15 at 21:46

score 36 · Answer 3 · answered Dec 02 '08 at 14:36

Select              
CASE
    WHEN listing.OfferText is null or listing.OfferText = '' THEN company.OfferText
    ELSE COALESCE(Company.OfferText, '')
END As Offer_Text,         
from tbl_directorylisting listing  
 Inner Join tbl_companymaster company            
  On listing.company_id= company.company_id

score 18 · Answer 4 · edited Jul 13 '10 at 08:30

18

Here is another solution:

SELECT Isnull(Nullif(listing.offertext, ''), company.offertext) AS offer_text, 
FROM   tbl_directorylisting listing 
       INNER JOIN tbl_companymaster company 
         ON listing.company_id = company.company_id

edited Jul 13 '10 at 08:30

Anax

9,122
5
34
68

answered Mar 24 '09 at 13:35

score 14 · Answer 5 · edited Mar 07 '12 at 15:26

14

You can use ISNULL and check the answer against the known output:

SELECT case when ISNULL(col1, '') = '' then '' else col1 END AS COL1 FROM TEST

edited Mar 07 '12 at 15:26

Adriano Carneiro

57,693
12
90
123

answered Dec 02 '08 at 14:38

Code Trawler

323
2
7

score 13 · Answer 6 · edited Mar 18 '16 at 16:54

13

In SQL Server 2012 you have IIF, e.g you can use it like

SELECT IIF(field IS NULL, 1, 0) AS IsNull

The same way you can check if field is empty.

edited Mar 18 '16 at 16:54

ΩmegaMan

29,542
12
100
122

answered Aug 01 '14 at 06:13

lkurylo

1,621
33
59

score 7 · Answer 7 · answered Sep 14 '15 at 17:57

7

Use the LEN function to check for null or empty values. You can just use LEN(@SomeVarcharParm) > 0. This will return false if the value is NULL, '', or ' '. This is because LEN(NULL) returns NULL and NULL > 0 returns false. Also, LEN(' ') returns 0. See for yourself run:

SELECT 
 CASE WHEN NULL > 0 THEN 'NULL > 0 = true' ELSE 'NULL > 0 = false' END,
 CASE WHEN LEN(NULL) > 0 THEN 'LEN(NULL) = true' ELSE 'LEN(NULL) = false' END,
 CASE WHEN LEN('') > 0 THEN 'LEN('''') > 0 = true' ELSE 'LEN('''') > 0 = false' END,
 CASE WHEN LEN(' ') > 0 THEN 'LEN('' '') > 0 = true' ELSE 'LEN('' '') > 0 = false' END,
 CASE WHEN LEN(' test ') > 0 THEN 'LEN('' test '') > 0 = true' ELSE 'LEN('' test '') > 0 = false' END

answered Sep 14 '15 at 17:57

Zach Johnson

682
7
14

When you say "... LEN(NULL) returns NULL and NULL > 0 returns false...", the true rule is that every test or comparaison with NULL return NULL ! – Didier68 Feb 17 '16 at 11:23
Right that is worth noting, but this works as a shortcut because the comparison makes the null coalesce to the correct boolean so this wouldn't work for the inverse comparison of LEN(NULL) = 0 when we want to return true for null or empty. – Zach Johnson Mar 22 '17 at 20:19

score 5 · Answer 8 · edited May 16 '19 at 11:00

5

I know this is an old thread but I just saw one of the earlier posts above and it is not correct.

If you are using LEN(...) to determine whether the field is NULL or EMPTY then you need to use it as follows:

...WHEN LEN(ISNULL(MyField, '')) < 1 THEN NewValue...

edited May 16 '19 at 11:00

Mansoor Ali

153
12

answered Aug 28 '17 at 18:02

Milan

3,209
1
35
46

score 4 · Answer 9 · answered May 14 '13 at 09:36

4

this syntax :

SELECT *
FROM tbl_directorylisting listing
WHERE (civilite_etudiant IS NULL)

worked for me in Microsoft SQL Server 2008 (SP3)

answered May 14 '13 at 09:36

Romain Durand

783
1
6
22

score 4 · Answer 10 · answered Sep 10 '13 at 05:52

4

Select              
Coalesce(NullIf(listing.OfferText, ''), NullIf(company.OfferText, ''), '') As Offer_Text,         
from tbl_directorylisting listing  
 Inner Join tbl_companymaster company            
  On listing.company_id= company.company_id

answered Sep 10 '13 at 05:52

Muhammad Sharjeel Ahsan

662
3
13
28

Plus one for the first answer (5 years later) to use both `NULLIF()` and coalesce to an empty string if `company.OfferText` is null. However, the 2nd `NULLIF()` call here serves no purpose, as if that value an empty string you're just going to coalesce back to an empty string anyway. – Joel Coehoorn Mar 18 '15 at 21:50

score 4 · Answer 11 · answered Jan 26 '16 at 22:11

This simple combination of COALESCE and NULLIF should do the trick:

SELECT             
  Coalesce(NULLIF(listing.OfferText, ''), company.OfferText) As Offer_Text
...

Note: Add another empty string as the last COALESCE argument if you want the statement to return an empty string instead of NULL if both values are NULL.

score 3 · Answer 12 · edited Jan 15 '13 at 23:23

3

SELECT              
    COALESCE(listing.OfferText, 'company.OfferText') AS Offer_Text,         
FROM 
    tbl_directorylisting listing  
    INNER JOIN tbl_companymaster company ON listing.company_id= company.company_id

edited Jan 15 '13 at 23:23

Bo Persson

90,663
31
146
203

answered Jan 15 '13 at 23:09

Anoop Verma

1,495
14
19

score 3 · Answer 13 · answered Dec 02 '08 at 14:39

Here's a solution, but I don't know if it's the best....

Select              
Coalesce(Case When Len(listing.Offer_Text) = 0 Then Null Else listing.Offer_Text End, company.Offer_Text, '') As Offer_Text,         
from tbl_directorylisting listing  
 Inner Join tbl_companymaster company            
  On listing.company_id= company.company_id

score 3 · Answer 14 · answered Dec 10 '18 at 12:53

3

To check if variable is null or empty use this:

IF LEN(ISNULL(@var, '')) = 0
    -- Is empty or NULL
ELSE
    -- Is not empty and is not NULL

answered Dec 10 '18 at 12:53

Fábio Nascimento

2,644
1
21
27

score 2 · Answer 15 · edited Jul 28 '12 at 15:02

2

To prevent the records with Empty or Null value in SQL result

we can simply add ..... WHERE Column_name != '' or 'null'

edited Jul 28 '12 at 15:02

Rndm

6,710
7
39
58

answered Jul 28 '12 at 11:02

Code Crawler

43
5

This would be the first port of call but if you are doing things like concatenating from multiple columns in a table for example, then this would exclude the row rather than just display a blank for that column – Paul C Jan 03 '13 at 12:42

score 2 · Answer 16 · edited Jul 17 '17 at 21:42

2

[Column_name] IS NULL OR LEN(RTRIM(LTRIM([Column_name]))) = 0

edited Jul 17 '17 at 21:42

Paul Roub

36,322
27
84
93

answered Jul 17 '17 at 21:28

user3829854

235
3
4

score 2 · Answer 17 · answered Nov 06 '19 at 13:50

2

[Column_name] > ' ' excludes Nulls and empty strings. There is a space between the single quotes.

answered Nov 06 '19 at 13:50

Hicham

31
1

Vedran · Answer 18 · 2020-10-08T08:57:14.810

When dealing with VARCHAR/NVARCHAR data most other examples treat white-space the same as empty string which is equal to C# function IsNullOrWhiteSpace.

This version respects white-space and works the same as the C# function IsNullOrEmpty:

IIF(ISNULL(DATALENGTH(val), 0) = 0, whenTrueValue, whenFalseValue)

Simple test:

SELECT
    '"' + val + '"' AS [StrValue],
    IIF(ISNULL(DATALENGTH(val), 0) = 0, 'TRUE', 'FALSE') AS IsNullOrEmpty
FROM ( VALUES 
    (NULL), 
    (''), 
    (' '), 
    ('a'), 
    ('a ')
) S (val)

score 0 · Answer 19 · edited Nov 06 '19 at 17:09

0

This caters for spaces as well.

(len(rtrim(ltrim(isnull(MyField,'')))) !=0

edited Nov 06 '19 at 17:09

ChrisM

505
6
18

answered Oct 30 '19 at 00:43

ramit girdhar

2,272
1
25
26

How do I check if a Sql server string is null or empty

19 Answers19

Linked