Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Journal of LTjake (4001)

Wednesday March 12, 2003
12:46 PM

LEN(NULL)

[ #11008 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • by autarch (914) on 2003.03.12 14:00 (#17909) Homepage Journal
    Welcome to the wonderful world of SQL, a place where we're stuck with extremely complex 3-value logic instead of pleasantly simple 2-value logic. Also, let's not forget all the other joys SQL brings us, such as duplicate rows, crapulous support for declarative integrity constraints, no useful user-defined data types, no support for queries of hierarchical data, and twenty-seven different ways to write any query.

    Oh joy, oh joy!
  • If all you want to do is return rows with some content in a field, I don't understand why where len(field) > 0 wouldn'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) = 0 to return null rows.

    • 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