Oracle null safe comparison

Thanks again for all your words of wisdom over the years, both here and in your books - you've helped me so much in my Oracle career. Cheers, Rob. Buh, haven't really thought about it It does give you a way of efficiently joining on nulls relational moderately-purists insert shudder here by creating a function-based index on NVL col1,CHR 0. Actually for purity's sake I might go with Coalesce col1,Chr 0 , since Nvl offers nothing that Coalesce doesn't improve on except screen ink.

I suppose it's theoretically possible to store chr 0 in a varchar2. The paranoid might like to use a check constraint to prevent such an abomination. February 21, - pm UTC. I'm not a fan because it is relying on a "trick", and you are relying on an undocumented bit of behavior the chr 0 bit being a value not used to mean something.

June 27, - pm UTC. In our table, we can't figure out what one of the column values is. The column is a varchar2 1 and it looks like it's a blank or a null. It's neither a null or one blank space.

When we converted it to ascii, it converts to a zero and ascii 0 is considered a null in ascii format. Could you let me know what could have caused that and what an ascii 0 translates to? We are using Oracle version 8. August 12, - am UTC. Matt, December 02, - pm UTC. I picked up the following query from Cary Millsaps new book. I wondered what the purpose of CHR 0 was and when it might appear in the terminal column.

Any ideas? December 02, - pm UTC. Kurt, December 03, - am UTC. Tom, Comparing 2 values, each of which is possibly null, seems to be a common problem. But it can be used in SQL when needed. Do you have any further thoughts on it? Thanks, Kurt. December 03, - am UTC. Another 'benefit' of using DUMP to compare values is that if the datatype is different, the match fails, so you are compelled to do explicit datatype conversion. December 04, - am UTC. I do this in my audit triggers: IF new. CODE, :new.

I think its worth the tiny bit of typing it takes. Tom, I don't understand your response For example, consider a query that averages the five values , null, null, null, and Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null.

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal. Viewed 3k times. Below is my code. Add a comment. Active Oldest Votes. Gordon Linoff Gordon Linoff 1. Shaun Peterson Shaun Peterson 1, 1 1 gold badge 14 14 silver badges 18 18 bronze badges. Sign up or log in Sign up using Google.

Sign up using Facebook. Sign up using Email and Password.