Replace NULL values

By using this script, you can display what you want in place of null values. This is Oracle-specific syntax.

SQL > set null 
  

 

That's it! Here's an example:

SQL> SET NULL X 
SQL> SELECT ENAME,COMM FROM EMP; 

The result of this would be:

RESULT :- 
ENAME   COMM 
-----  ------ 
SCOTT    X 
KING    200 
AKRAM   500 
SMITH    X 

Reader Feedback

Lynn H. writes: Wouldn't it be easier to do the following:

SELECT ENAME, DECODE(COMM,'',COMM) AS COMM FROM EMP

The result of this would be:

RESULT :- 
ENAME   COMM 
-----  ------ 
SCOTT    X 
KING    200 
AKRAM   500 
SMITH    X 

Ravi G. writes: The following SQL statement will also do the same thing:

SQL> SELECT ENAME, NVL(COMM, 'X') COMM FROM EMP;

The result of this would be:

 
 RESULT :- 
 ENAME   COMM 
 -----  ------ 
 SCOTT    X 
 KING    200 
 AKRAM   500 
 SMITH    X 

For More Information

• What do you think about this tip? E-mail us at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts: Our SQL gurus are waiting to answer your toughest technical questions.