Oracle Pl/Sql String Compare Issue

Oracle PL/SQL string compare issue

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():

 DECLARE
 str1  varchar2(4000);
 str2  varchar2(4000);
 BEGIN
   str1:='';
   str2:='sdd';
-- 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;
 END;
 /

---

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 IS NOT NULL   NULL          FALSE
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.

Oracle PL/SQL string comparison on string literals: unexpected result

this is because 'MP' and 'MP ' are CHAR values and oracle automatically pads whatever value you assign to that variable with spaces to the maximum length specified

If you declare a CHAR variable with a length greater than 1, Oracle
Database automatically pads whatever value you assign to that variable
with spaces to the maximum length

see the OraMag article with similar to your example
http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51plsql-453456.html

BEGIN
  IF 'Logic' = 'Logic     '
  THEN
    DBMS_OUTPUT.put_line ('Equal');
  ELSE
    DBMS_OUTPUT.put_line ('Not Equal');
  END IF;
END;

show Equal because "Logic" was padded with spaces to "Logic "

PL SQL String comparison

|| is the string concatination operator, not the logical or operator I think you're assuming it is. So, your snippet actually means:

IF p_campaignPaymentType != 'per monthper creation' THEN
    RAISE ex_invalidPaymentType;
END IF;

But even if it were the logical or operator, it would still be wrong, as any string is either not equal to 'per month' or to 'per creation' (e.g., 'per month' is not equal to 'per creation'). Instead, you should use the logical and operator:

IF p_campaignPaymentType != 'per month' AND 
   p_campaignPaymentType != 'per creation' THEN
    RAISE ex_invalidPaymentType;
END IF;

Or, more elegantly, the not in operator:

IF p_campaignPaymentType NOT IN ('per month', 'per creation') THEN
    RAISE ex_invalidPaymentType;
END IF;

Oracle function to compare strings in a not ordered way

This can be done with a simple java function to sort the characters of a string alphabetically:

CREATE AND COMPILE JAVA SOURCE NAMED SORTSTRING AS
public class SortString {
  public static String sort( final String value )
  {
    final char[] chars = value.toCharArray();
    java.util.Arrays.sort( chars );
    return new String( chars );
  }
};
/

Which you can then create a PL/SQL function to invoke:

CREATE FUNCTION SORTSTRING( in_value IN VARCHAR2 ) RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'SortString.sort( java.lang.String ) return java.lang.String';
/

Then you can do a simple comparison on the sorted strings:

SELECT CASE
       WHEN SORTSTRING( 'ads' ) = SORTSTRING( 'das' )
       THEN 'Equal'
       ELSE 'Not Equal'
       END
FROM   DUAL;

Compare String in Oracle Case When

You are checking strings againts an empty string, thus having issues; in Oracle you'd better check if your string is not null:

SELECT CASE WHEN '7C54D3E133830A78E040A8C010014B7D' is not null
            THEN '7C54D3E133830A78E040A8C010014B7D'
            WHEN 'e84a4433966c4b8996ce34905acff63d' is not null
            THEN 'e84a4433966c4b8996ce34905acff63d'
            WHEN '7faa9126b1c6412fa58375ab2b2be1db' is not null
            THEN '7faa9126b1c6412fa58375ab2b2be1db'
            ELSE NULL
 END
 FROM DUAL 

About the way Oracle treats empty string and null, here you find something more

An example:

select q'['' = '']'         , case when '' = ''            then 'YES' else 'NO' end from dual union all
select q'['' is null]'      , case when '' is null         then 'YES' else 'NO' end from dual union all 
select q'['' = null ]'      , case when '' = null          then 'YES' else 'NO' end from dual union all 
select q'[null = null]'     , case when null = null        then 'YES' else 'NO' end from dual union all 
select q'[null is null]'    , case when null is null       then 'YES' else 'NO' end from dual union all 
select q'['' != '']'        , case when '' != ''           then 'YES' else 'NO' end from dual union all
select q'['' is not null]'  , case when '' is not null     then 'YES' else 'NO' end from dual union all
select q'['' != null ]'     , case when '' != null         then 'YES' else 'NO' end from dual union all
select q'[null != null]'    , case when null != null       then 'YES' else 'NO' end from dual union all
select q'[null is not null]', case when null is not null   then 'YES' else 'NO' end from dual

gives:

'' = ''           NO
'' is null        YES
'' = null         NO
null = null       NO
null is null      YES
'' != ''          NO
'' is not null    NO
'' != null        NO
null != null      NO
null is not null  NO

In a word, the only check you can rely on, when talking about NULL, is:
IS [NOT] NULL

Comparing two empty Strings in Oracle SQL

Oracle treats empty strings as NULL. It's a gotcha. Make a note of it and hope it never bites you in the butt in production.

SQL string comparison -how to ignore blank spaces

You can use trim on the column.

where trim(product_type) is null

The above is not DBMS-independent, since Sybase does not provide the trim function.
However, the below approach will work both in Sybase and Oracle:

where rtrim(ltrim(product_type)) is null

---

Related Topics