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 (http://blog.SQLAuthority.com) , BOL – LEN, BOL – DATALENGTH
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)