oracle pl sql string compare issue

oracle pl sql string compare issue  using -'sql,oracle,plsql,oracle10g,oracle11g'

1).Hello, I have the following Oracle PL/SQL codes that may be rusty from you guys perspective:

 str1  varchar2(4000);
 str2  varchar2(4000);
   IF(str1<>str2) THEN
    dbms_output.put_line('The two strings is not equal');
   END IF;

This is very obvious that two strings str1 and str2 are not equal, but why 'The two strings are not equal' was not printed out? Do Oracle have another common method to compare two string?

asked Sep 24, 2015 by akhilesh
0 votes

4 Answers

0 votes

As Phil noted, the empty string is treated as a NULL, and NULL is not equal or unequal to anything. If you expect empty strings or NULLs, you'll need to handle those with NVL():

 str1  varchar2(4000);
 str2  varchar2(4000);
-- Provide an alternate null value that does not exist in your data:
   IF(NVL(str1,'X') != NVL(str2,'Y')) THEN
    dbms_output.put_line('The two strings are not equal');
   END IF;

Concerning null comparisons:

According to the Oracle 12c documentation on NULLS, null comparisons using IS NULL or IS NOT NULL do evaluate to TRUE or FALSE. However, all other comparisons evaluate to UNKNOWN, not FALSE. The documentation further states:

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

A reference table is provided by Oracle:

Condition       Value of A    Evaluation
a IS NULL       10            FALSE
a IS NOT NULL   10            TRUE        
a IS NULL       NULL          TRUE
a = NULL        10            UNKNOWN
a != NULL       10            UNKNOWN
a = NULL        NULL          UNKNOWN
a != NULL       NULL          UNKNOWN
a = 10          NULL          UNKNOWN
a != 10         NULL          UNKNOWN

I also learned that we should not write PL/SQL assuming empty strings will always evaluate as NULL:

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

answered Sep 24, 2015 by badhwar.rohit
0 votes

Let's fill in the gaps in your code, by adding the other branches in the logic, and see what happens:

  2   str1  varchar2(4000);
  3   str2  varchar2(4000);
  4  BEGIN
  5     str1:='';
  6     str2:='sdd';
  7     IF(str1<>str2) THEN
  8      dbms_output.put_line('The two strings is not equal');
  9     ELSIF (str1=str2) THEN
 10      dbms_output.put_line('The two strings are the same');
 11     ELSE
 12      dbms_output.put_line('Who knows?');
 13     END IF;
 14   END;
 15  /
Who knows?

PL/SQL procedure successfully completed.


So the two strings are neither the same nor are they not the same? Huh?

It comes down to this. Oracle treats an empty string as a NULL. If we attempt to compare a NULL and another string the outcome is not TRUE nor FALSE, it is NULL. This remains the case even if the other string is also a NULL.

answered Sep 24, 2015 by shegokar.anjeet
0 votes

I compare strings using = and not <>. I've found out that in this context = seems to work in more reasonable fashion than <>. I have specified that two empty (or NULL) strings are equal. The real implementation returns PL/SQL boolean, but here I changed that to pls_integer (0 is false and 1 is true) to be able easily demonstrate the function.

create or replace function is_equal(a in varchar2, b in varchar2)
return pls_integer as
  if a is null and b is null then
    return 1;
  end if;

  if a = b then
    return 1;
  end if;

  return 0;
show errors

  /* Prints 0 */
  dbms_output.put_line(is_equal('AAA', 'BBB'));
  dbms_output.put_line(is_equal('AAA', null));
  dbms_output.put_line(is_equal(null, 'BBB'));
  dbms_output.put_line(is_equal('AAA', ''));
  dbms_output.put_line(is_equal('', 'BBB'));

  /* Prints 1 */
  dbms_output.put_line(is_equal(null, null));
  dbms_output.put_line(is_equal(null, ''));
  dbms_output.put_line(is_equal('', ''));
  dbms_output.put_line(is_equal('AAA', 'AAA'));
answered Sep 24, 2015 by rolvyrf
0 votes

To the first question:

Probably the message wasn't print out because you have the output turned off. Use these commands to turn it back on:

set serveroutput on
exec dbms_output.enable(1000000);

On the second question:

My PLSQL is quite rusty so I can't give you a full snippet, but you'll need to loop over the result set of the SQL query and CONCAT all the strings together.

answered Sep 24, 2015 by amit_cmps