Re: Example of a SQL function that returns a non-NULL value even if one of its parameters is NULL

Well, NVL for one (but that's kind-of designed for it ;-) Similarly NVL2.

Then there's REPLACE - if the "replacement string" parameter is null, then it removes occurrences of the search string (aka. replacing with empty string.)

DECODE works with nulls as well.

There may be others - that's just a quick browse through memory ;-)

Cheerio
/Kim

Regards

Kim Berg Hansen
Oracle ACE Director

Author of Practical Oracle SQL
<https://www.apress.com/gp/book/9781484256169> http://www.kibeha.dk
kibeha_at_kibeha.dk
_at_kibeha
<http://twitter.com/kibeha>

On Thu, Jan 12, 2023 at 7:21 AM Iggy Fernandez <iggy_fernandez_at_hotmail.com> wrote:

> Inbuilt SQL functions seem to return NULL if one of their parameters is
> NULL. Here are a couple of examples.
>
> INSTR(NULL, 'Hello, World!')
> INSTR('Hello, World!', NULL)
>
> Can anyone think of an inbuilt SQL function that returns a non-NULL value
> even if one of its parameters is NULL?
>
> Best,
> Iggy
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l