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

I can't help mentioning my pet peeve with vsize, which does return null for null due to the SQL standard despite knowing the storage size required for any type of null.  

we're at least 30 years and counting for Oracle supplying that builtin as a rejected enhancement request because "you can build it yourself."  

(Which, of course, I already had, but you can't really make a user defined
function as fast as or known to the CBO as a builtin.)  

Sigh.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark
Sent: Thursday, January 12, 2023 12:51 PM To: 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  

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> 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