Re: Comparing Null values in PL/SQL with varchar2

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 04 Sep 1999 08:50:09 -0400
Message-ID: <LRTRNw0O0Zk0ydYYrw3T9iGvbj47@4ax.com>

A copy of this was sent to "Jeroen Hogendoorn" <jeroenhog_at_gmx.net> (if that email address didn't require changing) On Sat, 4 Sep 1999 11:12:23 +0200, you wrote:

>I'm searching for an elegant way to compare two varchar2 vars, what i want
>is this
>
>var1 var2 result
>'' '' true
>null null true
>'aa' 'aa' true
>'a' 'b' false
>
>when using nvl() like this:
>
>a varchar2(10);
>b varchar2(10);
>
>if nvl(a,'blabla') = nvl(b,'blabla')
>then
> et etc.
>
>i always have to be sure that b or a cannot be 'blabla', can anybody tell me
>what a better solution is, WITHOUT writing all the code out ( if a is null )
>?????
>

write a function:

tkyte_at_8.0> create or replace function compvc( p_str1 in varchar2, p_str2 in varchar2 ) return
  2 boolean
  3 as
  4 return_val boolean default FALSE;   5 begin

  6     if ( p_str1 = p_str2 ) then
  7         return_val := true;
  8     elsif ( p_str1 is null and p_str2 is null ) then
  9         return_val := true;
 10     end if;
 11     return return_val;

 12 end;
 13 /

Function created.

tkyte_at_8.0>
tkyte_at_8.0> declare

  2      v1    varchar2(5) default 'aa';
  3      v2    varchar2(5) default 'aa';
  4  
  4      procedure testit
  5      is
  6      begin
  7          if ( compvc( v1, v2 ) ) then
  8              dbms_output.put_line( '"'||v1||'" = "'||v2||'"' );
  9          else
 10              dbms_output.put_line( '"'||v1||'" <> "'||v2||'"' );
 11          end if;
 12      end;
 13  begin
 14      testit;
 15      v1 := ''; v2 := '';
 16      testit;
 17      v1 := null; v2 := null;
 18      testit;
 19      v1 := 'a'; v2 := 'b';
 20      testit;

 21 end;
 22 /

"aa" = "aa"
"" = ""
"" = ""
"a" <> "b"

PL/SQL procedure successfully completed.    

>Please reply with e-mail: jeroenhog_at_gmx.net
>
>greetings and thanks in advance Jeroen
>
>
>
>
>
>
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Sep 04 1999 - 07:50:09 CDT