Re: String Compare in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 06 Oct 1998 14:14:56 GMT
Message-ID: <361d2516.6335109@192.86.155.100>

A copy of this was sent to hvande_at_sapient.com (if that email address didn't require changing) On Tue, 06 Oct 1998 13:40:20 GMT, you 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?
>

when you assign an empty string to a varchar, the result is null, consider:

SQL> declare
  2 v varchar2(5);
  3 begin

  4     v := '';
  5     if ( v is null ) then
  6        dbms_output.put_line( 'v is null' );
  7     end if;

  8 end;
  9 /
v is null

PL/SQL procedure successfully completed.

The code itself above (that only checks for != ) is buggy since NULLs introduce tri-valued logic. A might be equal to B, A might be not equal to B, it might be UNKNOWN whether A is equal/not equal to B.

The correct coding would be:

  1 declare
  2 v varchar2(5);
  3 begin

  4     v := '';
  5     if ( v <> 'Hello' ) then
  6        dbms_output.put_line( 'v Not Equal' );
  7     elsif ( v = 'Hello' ) then
  8       dbms_output.put_line( 'v is Equal' );
  9     else
 10       dbms_output.put_line( 'It is unknown...' );
 11     end if;

 12* end;
SQL> /
It is unknown...

PL/SQL procedure successfully completed.       

>Thanks,
>
>Henri
>hvande_at_sapient.com
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Oct 06 1998 - 09:14:56 CDT