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".
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".

回答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.

回答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;

回答3:

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.

回答4:

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;

回答5:

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

回答6:

how if the condition like this:

Select job_id, job_title, employee_id
from jobs join job_history using (job_id);

and show all of the employee_id who has been working in there, if is it not, replacing into varchar