3004.md

String Concatenation

Use COALESCE for string concatenation of nullable columns:

You may need to force a column or expression to be non-null for the sake of simplifying the query logic, but you don't want that value to be stored. Use COALESCE function to construct the concatenated expression so that a null-valued column doesn't make the whole expression become null.

Example

SELECT first_name || COALESCE(' ' || middle_initial || ' ', ' ') || last_name
AS full_name FROM Accounts