如何用文本替换空值？

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:

我需要在Oracle SQL中的employees表中显示Employee last_name及其佣金金额,但条件是如果遇到NULL值我需要打印“No Commission”。我写的第一部分是:

select last_name, commission_pct from employees;

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

但我无法得到如何用“无佣金”替换NULL值。

5 个解决方案

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

附:如果commision_pct的数据类型不是varchar,您还应该使用cast或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.

由于commision_pct是NUMBER数据类型,to_char会将其显式转换为字符串。

#4

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

选择Last_Name,解码(nvl(salarycommission_pct,'0'),0,'No Commission',salarycommission_pct)作为员工的COMM;

#5

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.

它很简单,你可以看到,Isnull()用于将NULL值替换为我们传递的默认值,所以我在这里做的是如果“commission_pct”具有NULL值,那么它将用“No Commission”文本替换它,我已经在ISNULL()中传递了第二个参数。

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

#1