How to replace null values with a text?

Question

I need to display Employee last_name and their commission amount from employees table in Oracle SQL, but the condition is if it encounter NULL value I need to print "No Commission".
For the first part I wrote:

select last_name, commission_pct from employees;

But I am unable to get how to replace NULL values with "No Commission".

Answer 1

You can use case expression:

select last_name
     , case when commision_pct is null then 'No Commission' else commision_pct end    
from employees;

or coalesce:

select last_name
     , coalesce(commision_pct, 'No Commission')
from employees;

or nvl:

 select last_name
     , nvl(commision_pct, 'No Commission')
from employees;

P.S. In case commision_pct's datatype is not varchar you should also use cast or to_char.

Answer 2

For Oracle

select last_name, nvl(commission_pct,'No Commission')
from employees;

For SQL

select last_name, isnull(commission_pct,"No Commission") as commission_pct
    from employees;