SQL 如何用文本替换空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29982375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to replace null values with a text?
提问by Rishabh Prasad
I need to display Employee last_nameand their commission amount from employees table in Oracle SQL, but the condition is if it encounter NULLvalue I need to print "No Commission".
For the first part I wrote:
我需要last_name在 Oracle SQL 中的员工表中显示员工及其佣金金额，但条件是如果遇到NULL值我需要打印"No Commission"。
对于第一部分，我写道：
select last_name, commission_pct from employees;
But I am unable to get how to replace NULLvalues with "No Commission".
但我无法获得如何NULL用"No Commission"替换值。
回答by potashin
You can use caseexpression:
您可以使用case表达式：
select last_name
, case when commision_pct is null then 'No Commission' else commision_pct end
from employees;
or coalesce:
或coalesce：
select last_name
, coalesce(commision_pct, 'No Commission')
from employees;
or nvl:
或nvl：
select last_name
, nvl(commision_pct, 'No Commission')
from employees;
P.S. In case commision_pct's datatype is not varcharyou should also use castor to_char.
PS 如果commision_pct数据类型不是，varchar您还应该使用castor to_char。
回答by Jaimin Soni
For Oracle
甲骨文
select last_name, nvl(commission_pct,'No Commission')
from employees;
For SQL
对于 SQL
select last_name, isnull(commission_pct,"No Commission") as commission_pct
from employees;
回答by Chirag Thakar
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 值，那么它将用“无佣金”文本替换它，我已将 ISNULL() 作为第二个参数传递。
select last_name,
ISNULL(commission_pct,'No Commission') AS commission_pct
from employees;
回答by Lalit Kumar B
Another alternative, quite simple and precise:
另一种选择，非常简单和精确：
nvl(to_char(commision_pct), 'No Commission')
Since, commision_pctis NUMBERdata type, to_charwill explicitly convert it into string.
由于，commision_pct是NUMBER数据类型，to_char将其显式转换为字符串。
回答by ajeesh
select Last_Name, decode(nvl(salarycommission_pct,'0'),0,'No Commission',salarycommission_pct) as COMM from employees;
选择 Last_Name, decode(nvl(salary Commission_pct,'0'),0,'No Commission',salaryCommission_pct) 作为来自员工的 COMM；
回答by Kinan
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
并显示所有在那里工作的employee_id，如果不是，则替换为 varchar
