All the Perl that's Practical to Extract and Report
use Perl Log In
LTjake (4001)
LEN(NULL)
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.
an endless discussion thread about what features to include.
-- Jarkko Hietaniemi
Welcome to SQL (Score:3, Funny)
Oh joy, oh joy!
Reply to This
Re:Welcome to SQL (Score:2)
Re:Welcome to SQL (Score:2)
Why doesn't len(field) 0 work? (Score:1)
where len(field) > 0wouldn't work whether or not field is null. Unless there's something strange about MS SQL Server I don't know, or I've been working with Informix for too long.Now it would be a different matter if you wanted
where len(field) = 0to return null rows.Re:Why doesn't len(field) 0 work? (Score:1)
Hey. =)
You're right. That DOES work. From what i read, i had gotten the impression that doing a numeric comparison with NULLs generated some funky results. But, in this case, it works like a charm, Thanks! I guess i was too fixated on dealing with blank entries, rather than getting non-blank entries :)
-Brian