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 ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Wednesday August 13, 2003
10:03 AM

gem of the day: consistently handling NULL

[ #14086 ]

Long overdue, today's Oracle gem of the day is actually an ANSI SQL-92 gem of the day. This answers a question I've had for a long time: how do you consistently handle NULLs across databases?

One thing I heard awhile back is that Oracle uses this strange "VARCHAR2" type instead of the normal VARCHAR because there is some aspect of VARCHAR that is undefined in the standard. Oracle's base type is VARCHAR2, and currently VARCHAR is just a synonym. If the standard ever gets clarified and it is different from the choice Oracle made to resolve the ambiguity, Oracle's VARCHAR will change while VARCHAR2 will stay the same. I'm under the impression that the main difference is sorting NULLs: some implementations put NULLs before non-NULLs when sorting, some do otherwise. (I may be completely wrong about all this.)

So I've often wondered what I could do in SQL to make NULLs sort the same in code that might be moved to different databases. The answer (well, the standards-based answer, and we all know what the great thing about standards is) is the SQL-92 CASE statement.

Oracle gives you a nice routine called NVL to provide default replacement values for NULLs. NVL is sort of like a defined-or operator for Perl. If everybody gave you NVL, you could just wrap all your possibly NULL columns in NVL and things would work identically across databases. Unfortunately NVL is not part of the standard, and every RDBMS gives you a different function to achieve this.

However, the ANSI standard CASE statement fits the bill. The following will select a column from a table turning all NULLs into the string "NONE":

SELECT CASE column
        WHEN NULL THEN 'NONE'
        ELSE column
       END
FROM table;

Lovely, and it works everywhere! Actually, that's a complete lie. This was added into the SQL standard in 92. Oracle adopted it 8 years later, and I'll bet other databases have similar track records with compliance. If you have Oracle 8.1.6 or later, you can use this in SQL (must have Oracle 9i or later to use it in PL/SQL), and someday all databases will support it. If they feel like it.

(Note: I haven't completely tested this, yet, and I think there might be some issues with the difference between NULL, a null string, and a string full of spaces. YMWV.)

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.
  • Uses the (I think) ANSI compliant COAELESCE() instead of NVL. It's a handy toy to know about. Just today I had to use it to convert NULLs to 0 for comparison purposes.

    -Dom

  • porting DECODE (which was what most Oracle SQL programmers used before CASE was implemented) into MySQL.. scarred me for life, it did. Because PL/SQL programmers uses DECODE in lots of funky ways..

    • which was what most Oracle SQL programmers used before CASE was implemented

      I'm sure it will be what most Oracle programmers use after CASE is implemented. It took eight years from standardization for Oracle to get it into the system ... figure twice that for general users to figure it out and start using it.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers