Concatenating with NULLs in SQL

A concatenation of 2 or more values in SQL looks like a pretty simple operation. It's enough to use '||' operator which comes in SQL standard. Well, it turns out that this is not so trivial in case of NULL values.

Today I was struggling with SQL function which looked perfectly fine but for some reason it didn't work as expected. In many cases (but not all) it returned NULL, though it shouldn't. After debugging the code I've found out that the problem was caused by a concatenation. In my function I had the code like this:

DECLARE
  record RECORD;
  result_text text;
BEGIN
  ...
  IF ([condition1]) THEN
    result_text = '[some_text]'
  END IF;

  FOR record IN (select t.name from table t) LOOP
    IF ([condition2]) THEN
      result_text = result_text || record.name;
    END IF;
  END LOOP;
  ...
END;

Whenever condition1 was met and result_text variable was initiated, everything worked fine in the loop. But when condition1 was evaluated to false and variable was not initialized, the concatenation in the loop always resulted in NULL value. It turns out that the result of val1 || val2 operation when one of the values is NULL will always be NULL. This came as a surprise to me as I thought that NULL || 'some text' will rather return 'some text', not NULL. Ok, so what can we do about it? The simplest solution is to replace '||' operator with concat() function. concat(NULL, 'some text') will produce 'some text' which is what we want. The concat() function is available in most of SQL dialects including postgresql and transact-sql. If we don't have access to concat() function then we can use SELECT CASE clause as a replacement:

SELECT CASE
  WHEN val1 IS NULL THEN val2
  WHEN val2 IS NULL THEN val1
  ELSE val1 || val2
END