CODEWITHSUNDEEP
sqldatabaseoracle-databasesyntax
Chintz
Chintz

Reputation: 61

PL/SQL Relational operator <> , != Syntax issue

I am trying below code in pl/sql program. I didn't get my code running through any of the below condition, it just skips all the 'if' conditions.

Code snap :---

IF(null != 'C') THEN 
       DBMS_OUTPUT.put_line ('1');
 END IF;

 IF(Trim('') <> 'C') THEN 
       DBMS_OUTPUT.put_line ('2');
 END IF;

 IF(Trim('') != 'C') THEN 
       DBMS_OUTPUT.put_line ('3');
 END IF;

Upvotes: 0

Views: 310

Answers (3)

GolezTrol
GolezTrol

Reputation: 116110

Unlike in other databases, in Oracle an empty string ('') is actually the same as NULL, so Trim('') is NULL too. Therefor, all three if statements actually have the same condition.

Normal comparison operators don't work for null

The normal comparison operators like =, <> and != don't work for NULL, so they also don't work for '' either. By don't work I mean, they always return false, regardless of the other operand. Every comparison with NULL will result in false, so X = null and X != null are both false. Sounds like quantum physics, doesn't it? :) This rule does apply to all databases, by the way.

IS and IS NOT for NULL

You can use the special is and is not operators for null, so the following two ifs are semantically the same (in Oracle):

if C is not null then

if C is not '' then

But this one is wrong:

if C != '' then

Because it actually means the following, and always returns false:

if C != null then

Most string functions return NULL as well for NULL input

This can be a huge nuisance, since even length('') returns null instead of 0: Easy to make a mistake there.

select length('') from dual -- Returns NULL

Extra tricky, because a 'normal' comparison with 0 returns false. So the following (accidentally) works

select 
  case when length(StringField) > 0 then
    'string is not empty'
  else
    'string is empty'
  end
from dual

But this one does not:

select 
  case when length(StringField) = 0 then
    'string is empty'
  else
    'string is not empty'
  end
from dual

Upvotes: 3

Robert
Robert

Reputation: 25753

For me every condition from your question means the same as: 

IF('C' is not null) THEN  
       DBMS_OUTPUT.put_line ('1');
 END IF;

This is properly way to compare your value with NULL

Trim('') means null, so this condition isn't correct 

Trim('') <> 'C'
Trim('') != 'C'

and this one isn't correct too

null != 'C'

Upvotes: 0

psaraj12
psaraj12

Reputation: 5072

In Oracle NULL cannot be compared with a value hence you can use the function NVL(x,y) in which if x has value the function returns x and if x is null it returns y

IF (NVL(NULL,'D') != 'C')
THEN
DBMS_OUTPUT.put_line ('1');
END IF;

IF(NVL(Trim(''),'D') <> 'C') THEN 
   DBMS_OUTPUT.put_line ('2');
END IF;

IF(NVL(Trim(''),'D') != 'C') THEN 
   DBMS_OUTPUT.put_line ('3');
END IF;

Upvotes: 0

Related Questions