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

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Thu, 12 Jan 2023 17:50:51 +0000
Message-ID: <CO1PR01MB67091F6E132D158D9CBC0DB7CEFD9_at_CO1PR01MB6709.prod.exchangelabs.com>

Let add the ANSI standard coalesce function to the list. Stealing JL's example

MPOWEL01> select coalesce(null,'a') C1, coalesce('b',null) C2 from dual;

C C
- -

a b

Mark Powell
Database Administration
(313) 592-5148

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jonathan Lewis <jlewisoracle_at_gmail.com> Sent: Thursday, January 12, 2023 6:53 AM To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> Subject: Re: Example of a SQL function that returns a non-NULL value even if one of its parameters is NULL

concat()

SQL> select concat('a',null) from dual;

C
-

a

1 row selected.

SQL> select concat(null,'a') from dual;

C
-

a

1 row selected.

Regards
Jonathan Lewis

On Thu, 12 Jan 2023 at 06:21, Iggy Fernandez <iggy_fernandez_at_hotmail.com<mailto: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 Received on Thu Jan 12 2023 - 18:50:51 CET