1 post tagged “sql”

I was playing around with MS SQL Server 2000 this morning. In a stored procedure I was creating, I wanted to eliminate NULL and empty ('') results from a certain column (chosen from a CASE statement). I wondered if perhaps I could simplify things if I only checked the length of the column thus reducing it to one comparison.

I should've figured that LEN( NULL ) would return NULL. What sucks even more is that LEN( NULL ) + 0 is still NULL, even casting to an INT via CAST( LEN( NULL ) AS INT ) is NULL

Luckily, there's the ISNULL function. You can ask it to give you something useful in return when the value of the column is NULL. I ended up with something like this:

...
WHERE ISNULL( LEN( column ), 0 ) <> 0 AND
...

(Substitute column for a CASE statement)

You'd think I would've seen this earlier... *SIGH*

Update: runrig has set me straight. WHERE LEN(column) > 0 is what i really wanted. Silly me. Thank you.