How To Replace Null Values With A Text?

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'

Solution 1:

You can use case expression:

select last_name
     , casewhen commision_pct isnull 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.

Solution 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;

Solution 3:

It is as simple as you can see, Isnull() Used to Replace NULL values to the default value we pass there, so what i did here is If "commission_pct" having NULL value then it'll replace that with "No Commission" text, which i have passed in ISNULL() as 2nd parameter.

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

Solution 4:

Another alternative, quite simple and precise:

nvl(to_char(commision_pct), 'No Commission') 

Since, commision_pct is NUMBER data type, to_char will explicitly convert it into string.

Solution 5:

select Last_Name, decode(nvl(salarycommission_pct,'0'),0,'No Commission',salarycommission_pct) as COMM from employees;