Re: String Compare in PL/SQL

From: <nasof_at_hotmail.com>
Date: Tue, 06 Oct 1998 18:10:32 GMT
Message-ID: <6vdmeo$af5$1@nnrp1.dejanews.com>

In article <6vd6k4$ium$1_at_nnrp1.dejanews.com>,   hvande_at_sapient.com wrote:
> I noticed an interesting issue in some of my PL/SQL code that I have not see
> before. Here is a snip of the code that describes the problem:
>
> declare
> v_text varchar2(200);
> begin
> v_text := '';
>
> if v_text != 'hello'
> then
> dbms_output.put_line('NOT Equal');
> else
> dbms_output.put_line('Equal');
> end if;
> end;
>
> When I run this program it gives me the result of 'Equal' which is NOT true.
> However is I specify a different value for v_text, e.g. v_text := 'foo'. Then
> the code displays 'NOT Equal'. My best guess is that Oracle translates an
> empty string to a NULL, which is not the same thing. So the question is: is
> this a bug or should I be doing something else?
>

Actually, in PLSQL an empty string and a null are the same for a varchar. You cannot properly equate the unknown (null) to something. Actually, your code snippet assumes that if something is NOT equal, then it must be equal. A more accurate test is:

declare
  v_text varchar2(200);
begin
  v_text := '';
  if v_text != 'hello'
  then

     dbms_output.put_line('NOT Equal');
  elsif v_text = 'hello' then
     dbms_output.put_line('Equal');
  else
     dbms_output.put_line('Other');

  end if;
end;

Which returns 'Other';

You could also try testing it for null values first, such as:

declare
  v_text varchar2(200);
begin
  v_text := '';
  if v_text != 'hello'
  then

     dbms_output.put_line('NOT Equal');
  elsif v_text = 'hello' then
     dbms_output.put_line('Equal');
  elsif v_text is null then
     dbms_output.put_line('Null');
  else
     dbms_output.put_line('Other');

  end if;
end;

-Frank

> Thanks,
>
> Henri
> hvande_at_sapient.com
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Oct 06 1998 - 13:10:32 CDT