Re: How to compare VARCHAR2 and CHAR?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 16 Oct 1998 18:21:11 GMT
Message-ID: <36298e0f.19892994@192.86.155.100>

A copy of this was sent to suisum_at_freenet.edmonton.ab.ca () (if that email address didn't require changing) On 16 Oct 1998 17:49:48 GMT, you wrote:

>A copy of this message sent to comp.oracle.misc
>
>Hi Thomas:
>
>I have trouble to compare the string values this time. I have the
>following codes to show my problem. I find that if I pass the string
>values to a function to do the comparision, it just can'ttell the NULL
>values. Would it be a problem becasue it is VARCHAR2 Vs. CHAR? Please
>help.
>
>Best regards,
>Still
>

No, the problem is NULLS in general. Your code assumes that v_match can take 1 of 2 values - TRUE or FALSE. but it takes three values -- TRUE, FALSE and NULL (or unknown). The result of

         v_match := (i_value1 = i_value2);

is NULL if either of i_value1 or i_value2 is NULL, not true, not false but NULL. You never test v_match for nullness, hence most

 	IF NOT v_match THEN
 	  v_match := i_value1 IS NULL AND i_value2 IS NULL;
 	END IF;

type of blocks never get executed since its neither true, nor false.

Why don't you just code a package as such:

create or replace package compare
as

   function isequal( x in number, y in number ) return boolean;
   function isequal( x in date, y in date ) return boolean;
   function isequal( x in varchar2, y in varchar2 ) return boolean;

end;
/

create or replace package body compare
as

function isequal( x in number, y in number ) return boolean is
begin

   return (x=y) OR (x is NULL and y is NULL ); end;

function isequal( x in date, y in date ) return boolean is
begin

   return (x=y) OR (x is NULL and y is NULL ); end;

function isequal( x in varchar2, y in varchar2 ) return boolean is
begin

   return (rtrim(x)=rtrim(y)) OR (x is NULL and y is NULL ); end;

end;
/

that should do it...

>PROCEDURE test2 IS
>
> i_char intl_cmpny_cp.cmpny_shrt_nam%TYPE;
> i_varchar internal_company_detail.short_name%TYPE;
>
> -- ======================================================
> FUNCTION is_equal_fn (
> i_value1 IN VARCHAR2,
> i_value2 IN VARCHAR2)
> RETURN BOOLEAN
> IS
> v_match BOOLEAN := FALSE;
> v_varchar1 VARCHAR2(2000);
> v_varchar2 VARCHAR2(2000);
>
> BEGIN
>
> DBMS_OUTPUT.PUT_LINE ('i_value1: ' || i_value1 ||
> 'i_value2: ' || i_value2);
>
> -- test for perfectly match.
> v_match := (i_value1 = i_value2);
>
> IF v_match THEN
> DBMS_OUTPUT.PUT_LINE ('v_match1 true');
> ELSE
> DBMS_OUTPUT.PUT_LINE ('v_match1 false');
> END IF;
>

[snip]

>END;
  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 Fri Oct 16 1998 - 13:21:11 CDT