Re: String Compare in PL/SQL

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:
> :
> 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?

Henri,

you are correct that an empty, ie zero length string ('') is converted to NULL. This is not an Oracle bug - by definition NULL is UNDEFINED therefore it is NEITHER EQUAL NOR INEQUAL to any other value. If NULL is a valid value in your program then, yes, YOUR program has a bug. You will need to handle NULL values properly. This can be done by using NVL() or actually testing for a NULL value:

Try changing the following line:

   if v_text != 'hello'

to this:

   if (v_text IS NULL) OR (v_text != 'hello')

or this:

   if NVL(v_text,'$') != 'hello'

Note: if you use NVL in this way you must be sure that the value that you choose for the NULL to be converted to can NEVER be the same as the string you are testing against. I have used '$', but you could use any string.

To safely compare two strings str1 and str2, either of which can be NULL or any other value, you can use the following syntax:

  if (str1 IS NULL and str2 IS NOT NULL) or
     (str1 IS NOT NULL and str2 IS NULL) or
     (str1 IS NOT NULL and str2 IS NOT NULL and str1 != str2)

I hope this helps.

--
Regards
Yuri McPhedran

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