Re: 'hello world' != '' does not work?

A comparison with null is null, not false. The following illustrates the result:

declare
  a boolean := true;
begin
  a := (null != '');
  if a = true then

     dbms_output.put_line('a is true.');   elsif a = false then

     dbms_output.put_line('a is false.');   elsif a is null then

     dbms_output.put_line('a is null.');   else

     dbms_output.put_line('Could not determine.');   end if;
end;
/

Save this as n.sql and then run it, the following is returned:

SQL> @n
a is null.

Frank Hubeny

John Russell wrote:

> The '' is interpreted as a NULL. Any comparison against a null value
> (even != ) is considered false.
>
> Some ways around it: check for LENGTH('hello world') > 0, or use the
> function result NVL('hello world', 'value to use if first value is
> null').
>
> John
>
> On Fri, 16 Mar 2001 23:21:53 GMT, jeffreyb_at_gwu.edu (Jeffrey Boulier)
> wrote:
>
> >
> >Hi folks,
> >
> > We're running into a strange problem with != . It works for
> >numerics, but not for strings.
> >
> >produser_at_PROM> select sysdate from dual where 1 != 0;
> >
> >SYSDATE
> >---------
> >16-MAR-01
> >
> >user_at_DB> select sysdate from dual where 'hello world' != '';
> >
> >no rows selected
> >
> >Any thoughts? Is != not valid for comparing strings in Oracle, or have we
> >run into a bug? Our database is 8.1.6.3.
> >
> > Yours Truly,
> > Jeffrey Boulier
>
> --
> Got an Oracle database question?
> Try the search engine for the database docs at:
> http://tahiti.oracle.com/
Received on Tue Mar 20 2001 - 01:38:28 CST