Oracle Empty String IS NULL

(Oracle Empty String ?? ) This is a very common error in Oracle PL/SQL programs. Remember, there is nothing like zero length string or empty string in Oracle. Oracle treats empty string (i.e. '') as NULL.

This type of mistake is common in SQL's WHERE clause, & PL/SQL's IF condition.

e.g.:

SQL's Where clause: WHERE TRIM(color_name) = ''

PL/SQL's where clause: IF TRIM(v_color_name) = '' THEN


As there is NO EMPTY STRING in Oracle, so always writ code dealing with empty string as:

SQL : WHERE TRIM(color_name) IS NULL

PL/SQL : IF TRIM(v_color_name) IS NULL THEN


Always consider following:

• Clause written as variable_name = '' , never RETURNS TRUE (if value of variable_name is empty string). Because in this case we are trying to compare NULL with NULL value.
  
• NVL(variable_name, '') = '' IS never TRUE. In such condition; always use some un-expected value of variable like NVL(v_color_name,'****') = '****'
  
• '' = '' never RETURNS true. In Where clause, of IF condition, this condition is always FALSE.
  
• If data is fetched from Database, then empty string is always returned as NULL value. So take special care when writing IF OR WHERE clause.
  

If you like this small article, be follower of code4coder.com and tell your fellows about this site. If you want to be part of Code4Coder.Com, then do tell us … Comments and suggestions are always welcomed.

Oracle, SQL, PL/SQL, Empty String, NULL, String comparision with NULL, NVL, NVL String, '', empty string, oracle empty string comparison, oracle string comparison, code4coder, code4coder.com, oracle, best performance, optimized code, common sql mistakes