Oracle - How to tell between null and not null string?

Question

In an anonymous block I have an input string that is empty/null and want to check that against a non-null string. Example:

DECLARE 
v_notnull varchar2(50):='this string is never null';
v_input varchar2(50):='';
BEGIN
    IF trim(v_input) != trim(v_notnull) THEN
        dbms_output.put_line('the strings do NOT match');
    ELSE 
        dbms_output.put_line('the strings DO match');
    END IF; 
END;

The issue here is that when I run this block, the output is always 'the strings DO match' even though I am inputting the empty string '' (aka null) into v_input which is not the same as the string 'this string is never null'. How can I make sure oracle covers the empty string case? When v_input is empty I want the output to be 'the strings do NOT match'.

The issue is that your code won't even compile, what do you mean by "the output is always..." ??? You are missing the length of the varchar2 variables you declared, and that is a fatal error. — , Feb 03 '17 at 19:01
OK folks.... I was typing that on a cell phone. I've fixed that in my question. — Hooplator15, Feb 03 '17 at 19:57
This is probably one of the most succinct references for how oracle handles null comparisons: http://www.morganslibrary.org/reference/null.html — Kris Johnston, Feb 03 '17 at 20:14

Alex Poole · Accepted Answer · 2017-02-03T19:04:10.307

The documentation has a section on null handling. An empty string is treated the same as null, and you cannot compare nulls (of any type) with equality - as the table in the documentation shows, the result of comparing anything with null is unknown - neither true nor false. You have to use is [not] null to compare anything with null.

In this case you could spell it out explicitly, by seeing is one variable is null and the other isn't, and vice versa, and only compare the actual values if that tells you neither are null:

DECLARE 
v_notnull varchar2(30):='this string is never null';
v_input varchar2(30):='';
BEGIN
    IF (trim(v_input) is null and trim(v_notnull) is not null)
      or (trim(v_input) is not null and trim(v_notnull) is null)
      or trim(v_input) != trim(v_notnull) THEN
        dbms_output.put_line('the strings do NOT match');
    ELSE 
        dbms_output.put_line('the strings DO match');
    END IF;
END;
/

the strings do NOT match


PL/SQL procedure successfully completed.

I've added the missing varchar2 sizes; presumably you based this on a procedure that took arguments without running what you were posting stand-alone...

score 2 · Answer 2 · answered Feb 03 '17 at 18:59

'' is NULL in oracle. So, any comparison with null will always result in false.

Demo:

SQL> DECLARE
  2  v_notnull varchar2(1000):='this string is never null';
  3  v_input varchar2(1000):='';
  4  BEGIN
  5      IF v_input is null THEN
  6          dbms_output.put_line('v_input is null');   -- should print because v_input is actually null
  7      END IF;
  8
  9      IF trim(v_input) = trim(v_notnull) THEN        -- always false because of null
 10          dbms_output.put_line('the strings do NOT match');
 11      ELSE
 12          dbms_output.put_line('the strings DO match');  -- so should print this
 13      END IF;
 14  END;
 15  /
v_input is null            -- verified
the strings DO match       -- verified

PL/SQL procedure successfully completed.

SQL>

score 1 · Answer 3 · answered Feb 04 '17 at 10:28

Often you only care whether the values match, in which case null values make no difference:

declare
    v_notnull varchar2(50) := 'this string is never null';
    v_input   varchar2(50) := '';
begin
    if trim(v_input) = trim(v_notnull) then
        dbms_output.put_line('the strings DO match');
    else
        dbms_output.put_line('the strings do NOT match');
    end if;
end;

In some cases you can simplify a comparison of nullable values using a coalesce() or nvl() expression:

if nvl(v_yesno,'N') <> 'Y' then ...

You might be able to use a dummy comparison value (although of course there is a risk that it will match an actual value, depending on the situation):

if nvl(somevalue, chr(0)) <> nvl(othervalue, chr(0)) then ...

By the way, you can distinguish between null and '' by copying the value to a char variable, as a '' will trigger its normally unhelpful blank-padding behaviour. I can't really think of a situation where this would be useful, but just for fun:

declare 
    v1 varchar2(1) := null;
    v2 varchar2(1) := '';

    c char(1);
begin
    c := v1;
    if v1 is null and c is not null then
        dbms_output.put_line('v1 = ''''');
    elsif c is null then
        dbms_output.put_line('v1 is null');
    end if;

    c := v2;
    if v2 is null and c is not null then
        dbms_output.put_line('v2 = ''''');
    elsif c is null then
        dbms_output.put_line('v2 is null');
    end if;

end;

Output:

v1 is null
v2 = ''

Oracle - How to tell between null and not null string?

3 Answers3