Does this implementation comply with SQL-92?

Question

Tony Andrews in another question gave an example:

IF p_c_courtesies_cd 
   || p_c_language_cd 
   || v_c_name 
   || v_c_firstname 
   || v_c_function 
   || p_c_phone 
   || p_c_mobile p_c_fax 
   || v_c_email is not null
THEN
     -- Do something
END IF;

as a smart (if not the same incomprehensible) alternative to the Oracle COALESCE function. Of course, it works, if any argument is non-zero, the IF check is true. My question is: Is the Oracle implementation of the above SQL-92 concatenation operation? Should an expression with NULL evaluate to NULL? If you don’t think so, then why is the 1 + NULL expression evaluated to NULL?

Answer 1

, Oracle , ANSI. , Oracle , , , ANSI!

, Oracle , . NULL , , - , (''). Oracle .

, . Oracle LENGTH, , , LENGTH ('') NULL, . :

LENGTH('abc') + LENGTH('') IS NULL

LENGTH('abc' || '') = 3

, , .

, Oracle , - , NULL - !

Answer 2

@Nezroy: . , , , , Oracle . 6.13, , 2a:

     2) If <concatenation> is specified, then let S1 and S2 be the re-
        sult of the <character value expression> and <character factor>,
        respectively.

        Case:

        a) If either S1 or S2 is the null value, then the result of the
          <concatenation> is the null value.

Answer 3

COALESCE SQL-92 , NULL ; , , Oracle .

EDIT: SQL-92; COALESCE, .

, NULL, , , NULL, NULL. , NULL , 0, NULL (, NULL == NULL , NULL NULL). , , , NULL, NULL.

EDIT: , NULL + 1 NULL , NaN + 1 NaN; undefined.

Answer 4

SQL-92, DCookie , , Oracle .

Oracle ( tuinstoel):

SQL>  select 'something'||null from dual;

'SOMETHIN
---------
something

MSSQL:

SELECT 'something'+NULL;

NULL

PostgreSQL:

postgres=# \pset null '(null)'
Null display is "(null)".
postgres=# select 'something'||null as output;
 output
--------
 (null)
(1 row)

MySQL:

mysql> select concat('something',NULL) as output;
+--------+
| output |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

Answer 5

SQL>  select 'something'||null from dual;

'SOMETHIN
---------
something

concatenating a string using null does not result in a null value. I think this is normal behavior, I'm used to it. I don’t know what else to say.