Simple but interesting – In recent survey I found that many developers making this generic mistake. I have seen following code in periodic code review. (The code below is not actual code, it is simple sample code)
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)
…
I decided to send following code to them. After running the following sample code it was clear that LEN of NULL values is not 0 (Zero) but it is NULL. Similarly, the result for DATALENGTH function is the same. DATALENGTH of NULL is NULL.
Sample Test Version:
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)
PRINT 'LEN of NULL is 0'
ELSE
PRINT 'LEN of NULL is NULL'
Result Set:
LEN of NULL is NULL
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
7 Comments. Leave new
Hi,
On googling this problem i was lead to your site.. and you have correctly identified the problem.
To help out other visitors, the solution is simply COALESCE the value before checking the LEN
eg:
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(COALESCE(@MyVar, ”)) = 0)
We can use ISNULL as well
DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
SELECT LEN(ISNULL(@MyVar, ”))
Looks better (imho) ;
if isnull( LEN(@MyVar) , 0 ) = 0
works the same… :)
Len should work on the string instead of null so first replacing NULL with empty string and then performing length operation seems good choice IMHO :).
what about date fields?
DECLARE @MyVar datetime
SET @MyVar = NULL
SELECT LEN(ISNULL(@MyVar, ”))
?
If you have a number instead, you should cast it to varchar if you want the lenght, else Len function in Sql Server will tell you that coalesce(youtrInt, ”) is 1 ,and same will do ISNULL:
declare @myVar int
SET @myVar = NULL
SELECT LEN(ISNULL(@myVar, ”))
=return 1
–instead
SELECT LEN(ISNULL(cast(@myVar, varchar(10), ”))
return 0
but you can use LEN(colName) > 0 to eliminate both empty strings and nulls and retrieve only rows with valid text.