How to compare VARCHAR2 and CHAR?

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

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;



	-- If not match, test for NULL values.


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

	IF v_match THEN
	  DBMS_OUTPUT.PUT_LINE ('v_match2 true');
	ELSE
	  DBMS_OUTPUT.PUT_LINE ('v_match2 false');
	END IF;



	-- If still not match, trim the string and test it all over.


	IF NOT v_match THEN
	  v_match := (RTRIM(i_value1) = RTRIM(i_value2));
	END IF;

	IF v_match THEN
	  DBMS_OUTPUT.PUT_LINE ('v_match3 true');
	ELSE
	  DBMS_OUTPUT.PUT_LINE ('v_match3 false');
	END IF;



	-- If not match, test for NULL values.


	IF NOT v_match THEN
	  v_varchar1 := LTRIM(RTRIM(i_value1, ' '), ' ');
	  v_varchar2 := LTRIM(RTRIM(i_value2, ' '), ' ');
	  IF  v_varchar1 IS NULL
	  AND v_varchar2 IS NULL THEN
	    v_match := TRUE;
	  END IF;
	END IF;

	IF v_match THEN
	  DBMS_OUTPUT.PUT_LINE ('v_match4 true');
	ELSE
	  DBMS_OUTPUT.PUT_LINE ('v_match4 false');
	END IF;



	-- If not match, test for NULL values.


	IF NOT v_match THEN
	  v_varchar1 :=
		NVL(LTRIM(RTRIM(i_value1, ' '), ' '), '`');
	  v_varchar2 :=
		NVL(LTRIM(RTRIM(i_value2, ' '), ' '), '`');
	  IF  v_varchar1 = v_varchar2 THEN
	    v_match := TRUE;
	  END IF;
	END IF;

	IF v_match THEN
	  DBMS_OUTPUT.PUT_LINE ('v_match5 true');
	ELSE
	  DBMS_OUTPUT.PUT_LINE ('v_match5 false');
	END IF;



	-- If not match, test for NULL values.


	IF NOT v_match THEN
	  IF NVL(RTRIM(i_value1, ' '), '/') =
		NVL(RTRIM(i_value2, ' '), '/') THEN
		v_match := TRUE;
	  END IF;
	END IF;

	IF v_match THEN
	  DBMS_OUTPUT.PUT_LINE ('v_match6 true');
	ELSE
	  DBMS_OUTPUT.PUT_LINE ('v_match6 false');
	END IF;

	RETURN v_match;

   END is_equal_fn;

• Mainline ---------------------------------------- BEGIN

	i_char	  := 'ABCD';
	i_varchar := 'ABCD';
	IF is_equal_fn(i_char, i_varchar) THEN
	  DBMS_OUTPUT.PUT_LINE ('OK');
	END IF;	  

	i_char	  := 'ABCD     ';
	i_varchar := 'ABCD';
	IF is_equal_fn(i_char, i_varchar) THEN
	  DBMS_OUTPUT.PUT_LINE ('OK');
	END IF;	  

	i_char	  := '    ';
	i_varchar := ' ';
	IF is_equal_fn(i_char, i_varchar) THEN
	  DBMS_OUTPUT.PUT_LINE ('OK');
	END IF;	  

	i_char	  := '    ';
	i_varchar := NULL;
	IF is_equal_fn(i_char, i_varchar) THEN
	  DBMS_OUTPUT.PUT_LINE ('OK');
	END IF;	  

	i_char	  := NULL;
	i_varchar := NULL;
	IF i_char IS NULL AND i_varchar IS NULL THEN
	  DBMS_OUTPUT.PUT_LINE ('From mainline - ALL NULL');
	END IF;
	IF is_equal_fn(i_char, i_varchar) THEN
	  DBMS_OUTPUT.PUT_LINE ('OK');
	END IF;