Menu
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?
nvl(instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),' '), length(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5))))
This is going as a parameter to a substr function.
If instr(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5),' ') is != 0 then I want that value, otherwise I want the value of length(substr(ovrflo_adrs_info,instr(ovrflo_adrs_info,'bldg')+5))
is there an easy way to do this?
519
NVL. The NVL function allows you to replace null values with a default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged.
Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function.
The Oracle NULL Value (NVL) SQL operator is a great way to substitute NULL values with numeric values in Oracle SQL statements. As you can see, the NVL function replaces a NULL with whatever value you desire.
NVL and COALESCE are used to achieve the same functionality of providing a default value in case the column returns a NULL. The differences are: NVL accepts only 2 arguments whereas COALESCE can take multiple arguments. NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value.
You can use NVL(NULLIF(A,'0'), B)
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
