How to replace null values ​​with text?

Question

I need to display Employee last_name and their commission amount from the employee table in Oracle SQL, but this condition if it meets a NULL value I need to print "No Commission".
In the first part I wrote:

 select last_name, commission_pct from employees; 

But I can’t get how to replace the 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; 

PS In case the commision_pct type commision_pct 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; 

Answer 3

Another alternative, quite simple and accurate:

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

Since commision_pct is of type NUMBER , to_char will explicitly convert it to a string.

Answer 4

It's as simple as you can see, Isnull () is used to replace NULL values ​​with the default value that we go through there, so what I did here is If "Commission_pct" is NULL, then it will replace the text "No commission" , which I passed in ISNULL () as the second parameter.

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