Geek Answers Handbook

Oracle if 0, e.g. if null (nvl)

oracle has a nice built-in function to execute if null, however I want to do if = 0; is there an easy way to do this?

nvl (instr (substr (ovrflo_adrs_info, instr (ovrflo_adrs_info, 'bldg') + 5), ''), Length (ziZbg (ovrflo_adrs_info, INSTR (ovrflo_adrs_info, 'zd') + 5))))

This happens as a parameter to the substr function.

If instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),' ') is != 0 , then I want this value, otherwise I want the value length(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5))

is there an easy way to do this?

+4
source share
4 answers

I think you will need to use CASE

eg.

 WHEN instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),' ') != 0 THEN length(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5)) ELSE Some Default END as foo
+4
source

You can use NVL(NULLIF(A,'0'), B)

+8
source

You can technically do this with less typing:

 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 usually speak with Conrad and advise you to use CASE, so it’s easier to say that the purpose of the code is for future maintenance.

+5
source

I found that both answers are hard to read due to extra words from the original post. To summarize Konrad and Craig:

To replicate nvl (A, B), but instead of 0 instead of 0, follow these steps:

 WHEN A != 0 THEN A ELSE B END

or Craig is more compact (but harder for others to read):

 NVL(NULLIF(A,0),B)

I think the following will also work:

 DECODE(A,0,B,A)
+4
source

More articles:


All Articles