String Concatenation with nulls on Snowflake SQL

Question

I have 3 columns (first name, middle name, last name) and I want to concatenate the 3 strings (to construct a full name).

However, if any of these values is null, the result is null.

What's an elegant and safe way of concatenating strings, when some of them might be null?

Answer 1

A combination of NVL()/IFNULL() and CONCAT_WS() would work, but I prefer the following:

select array_to_string(array_construct_compact(column1, column2, column3), ' ')
from values('a', 'b', 'c'), ('a', null, 'c')

• array_construct_compact() removes nulls.
• array_to_string() adds the necessary spaces when concatenating.

https://docs.snowflake.com/en/sql-reference/functions/array_construct_compact.html