oracle if 0, like if null (nvl)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6205007/
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
oracle if 0, like if null (nvl)
提问by kralco626
oracle has a nice built in function for doing if null, however I want to do if = 0; is there a simple way to do this?
oracle 有一个很好的内置函数，用于 if null，但是我想做 if = 0; 有没有一种简单的方法可以做到这一点？
nvl(instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),' '), length(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5))))
nvl(instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),''),长度(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5))))
This is going as a parameter to a substr function.
这将作为 substr 函数的参数。
If instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),' ')is != 0then I want that value, otherwise I want the value of length(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5))
如果instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),' ')是!= 0那么我想要那个值，否则我想要的值length(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5))
is there an easy way to do this?
是否有捷径可寻？
采纳答案by Conrad Frix
回答by patricK
You can use NVL(NULLIF(A,'0'), B)
您可以使用 NVL(NULLIF(A,'0'), B)
回答by Nathan
I found both answers hard to read because of the extra verbiage from the original post. Summarizing Conrad and Craig's answers:
由于原始帖子中的多余文字，我发现这两个答案都难以阅读。总结康拉德和克雷格的回答：
To replicate nvl(A,B) but for 0 instead of null, you can do:
要复制 nvl(A,B) 但对于 0 而不是 null，您可以执行以下操作：
WHEN A != 0 THEN
A
ELSE
B
END
or Craig's more compact (but harder for others to read):
或 Craig 的更紧凑（但其他人更难阅读）：
NVL(NULLIF(A,0),B)
I think that the following would also work:
我认为以下内容也有效：
DECODE(A,0,B,A)
回答by Craig
You could technically do this with less typing as:
从技术上讲，您可以通过较少的输入来做到这一点：
nvl(
nullif(instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),' '),0),
length(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5))
)
However, I would typically side with Conrad and advise you to use CASE so it is easier to tell what the intent of the code is for future maintenance.
但是，我通常会站在 Conrad 一边，并建议您使用 CASE，这样可以更容易地判断代码的意图是为了将来的维护。
