CONCAT and NULL

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

CONCAT and NULL

Post by JimKnicely » 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:

Image

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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”