All the Perl that's Practical to Extract and Report

LEN(NULL)

LTjake on 2003-03-12T17:46:29

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.

Welcome to SQL

autarch on 2003-03-12T19:00:08

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!

Re:Welcome to SQL

lachoy on 2003-03-13T03:41:42

And then if you access it through perl you get twenty-seven ways to use the data generated by the twenty-seven different ways you can write your query ;-)

Re:Welcome to SQL

autarch on 2003-03-13T06:49:54

It's a combinatorial explosion of fun! Wheeeeeeeeeeeeeeeeeeeeeeeeeeee!

Why doesn't len(field) 0 work?

runrig on 2003-03-13T18:08:43

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.

Re:Why doesn't len(field) 0 work?

LTjake on 2003-03-14T14:37:30

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