Concatenate With Null Values In Sql

Column1 Column2 ------- ------- apple juice water melon banana red berry I have a table which has two columns. Column1 has a group of w

Solution 1:

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

SELECT Column1 +COALESCE(Column2, '') ASResultFROM YourTable

Solution 2:

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

SELECTa || bFROMSomeTable;

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.

Solution 3:

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.

SELECTCOALESCE('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.

Solution 4:

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

Solution 5:

I'm not certain what you're using as your database, but I would look for a "coalesce" function for your particular SQL dialect and use that.