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
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL – LEN, BOL – DATALENGTH
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.