The Vertica Database Forums

Posted: **Tue Mar 19, 2013 4:06 pm**

Hi!

We all know that the CONCAT function is used to concatenate strings. But what happens when one of the strings is a NULL value? The answer to that question depends on the database…

Vertica, MySQL and SQL Server all return a NULL result:

Vertica:

Code: Select all

dbadmin=> SELECT concat('Rocket J. Squirrel', NULL);
concat
--------

(1 row)

MySQL:

Code: Select all

MySQL> SELECT concat('Bullwinkle J. Moose', NULL);
+-------------------------------------+
| concat('Bullwinkle J. Moose', NULL) |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set (0.00 sec)

SQL Server:

Whereas, both Oracle and PostgreSQL ignore NULLs:

Oracle:

Code: Select all

SQL> SELECT concat('Boris Badenov', NULL) FROM dual;

CONCAT('BORIS
-------------
Boris Badenov

PostgreSQL:

Code: Select all

SELECT concat('Fearless Leader', NULL);
concat
Fearless Leader

Have fun!

The Vertica Database Forums

CONCAT and NULL

CONCAT and NULL