!IsNullOrEmpty equivalent in SQL server

Question

I'm creating an SSRS report and during writing the query I look up the code logic for the data that needs to be retrieved in query. There is lots of usage of !String.IsNullOrEmpty method so I want to know what is the shortest and best way to do the equivalent check in SQL server?

WHERE t.Name IS NOT NULL OR T.Name != ''

or....

WHERE LEN(t.Name) > 0

which one is correct? Or is there any other alternative?

Just realize trailing spaces are ignored in SQL. So `' ' = ''` evaluates to true. — HoneyBadger, Sep 17 '19 at 07:41
@HoneyBadger see [this](https://stackoverflow.com/questions/1399844/sql-server-2008-empty-string-vs-space) — Cid, Sep 17 '19 at 07:45
Definitely avoid applying functions to your column. Also `T.Name <> ''` is the only thing you need in SQL Server, a `NULL` value will result to unknown and be treated as false implicitly. Read Roger's solution. — EzLo, Sep 17 '19 at 09:25

score 3 · Answer 1 · answered Sep 17 '19 at 09:03

There is no built-in equivalent of IsNullOrEmpty() in T-SQL, largely due to the fact that trailing blanks are ignored when comparing strings. One of your options:

where len(t.Name) > 0

would be enough as len() ignores trailing spaces too. Unfortunately it can make the expression non-SARGable, and any index on the Name column might not be used. However, this one should do the trick:

where t.Name > ''

P.S. For the sake of completeness, the datalength() function takes all characters into account; keep in mind however that it returns the number of bytes, not characters, so for any nvarchar value the result will be at least double of what you might expect (and with supplementary characters / surrogate pairs the number should be even higher, if my memory serves).

For the SARGable note, +1. Most people don't realize how important that is. — EzLo, Sep 17 '19 at 09:21

Alexander Volok · Accepted Answer · 2019-09-17T09:26:31.180

If the desired result is the simplest possible one-liner then:

WHERE  NULLIF(Name, '') IS NOT NULL

However, from a performance point of view following alternative is SARGable, therefore indexes potentially can be used to spot and filter out records with such values

WHERE  Name IS NOT NULL  AND Name != ''

An example:

;WITH cte AS (
 SELECT 1 AS ID, '' AS Name UNION ALL
 SELECT 2, '  ' UNION ALL
 SELECT 3, NULL UNION ALL
 SELECT 4, 'abc' 
)


SELECT * FROM cte
WHERE  Name IS NOT NULL  AND Name != ''

Results to:

ID  Name
---------
4   abc

Thin July · Answer 3 · 2019-09-17T08:51:31.513

0

Yes, you can use WHERE LEN(t.Name)>0. You can also verify as below:

-- Count the Total number of records

SELECT COUNT(1) FROM tblName as t

-- Count the Total number of 'NULL' or 'Blank' records

SELECT COUNT(1) FROM tblName as t WHERE ISNULL(t.Name,'')= ''

-- Count the Total number of 'NOT NULL' records

SELECT COUNT(1) FROM tblName as t WHERE LEN(t.Name)>0

Thanks.

edited Sep 17 '19 at 08:51

answered Sep 17 '19 at 08:29

Thin July

76
5

!IsNullOrEmpty equivalent in SQL server

3 Answers3