Concatenate with NULL values in SQL

A few posts I have made tagged MSSQL have been renamed to 'SQL' by a moderator. So I am assuming you are using MSSQL

COALESCE will return the FIRST non-null value.

SELECT COALESCE('a', NULL, 'c')

will only return 'a'

If you want Firstname + Lastname, where sometimes one or the other is NULL, use CONCAT. Concat adds the strings together and replaces NULLS with 0 length non-null value.

 SELECT CONCAT('a', NULL, 'c')

will return 'ac'

If you want Fn space + middle name space + LN, combine concatinate with CONCAT:

SELECT CONCAT('a' + ' ', NULL + ' ', 'c')

Will return 'a c'.

The space after middlename (null) is eliminated with the + and NULL.

NULL + ' ' is null.

So in cases where Middlename or Firstname is null, you won't get extra unwanted spaces.

If you are using MySq, use ifnull(Column2, '')

Use the COALESCE function to replace NULL values with an empty string.

SELECT Column1 + COALESCE(Column2, '') AS Result
    FROM YourTable

Standard SQL requires that string concatenation involving a NULL generates a NULL output, but that is written using the || operation:

SELECT a || b
  FROM SomeTable;

The output will be null if either a or b or both contains a NULL.

Using + to concatenate strings indicates that you are using a DBMS-specific extension. The behaviour might be the same as the standard requires - indeed, that seems to be the gist of your question.

Some DBMS - notably Oracle - tend to treat null strings as equivalent to empty strings; then you can concatenate away merrily. However, that behaviour is not strictly standard-compliant if the || operator is used.

Consider using COALESCE or NVL or IFNULL or some similar function to map the NULL to an empty string before concatenating.