Concatenate multiple fields where some contain null values (QGIS)

Question

In QGIS 3.18. In a shapefile attribute table, I need to create a field that concatenates several other fields with a comma delimiter. Some of those fields are NULL.

Currently my approach has been what I thought was the foolproof "Field1" || ', ' || "Field2" || ', ' || "Field3". If all 3 of these fields contain a non-NULL value, there is no problem (example: Field1=A, Field2=B, Field3=C, result=A, B, C)

However, if one or more fields are NULL, the result is also NULL. (example: Field1=A, Field2=NULL, Field3=C, result=NULL).

I suspect there is a way to account for NULL values in the field calculator without resorting to Python. I do not believe that concat or coalesce can help me here, but please correct me if I'm wrong.

Note: I'm not concerned with labels here, just attributes.

Answer 1

1. First possibility: use concat and replace double commas where you have empty entries:

   replace( concat (field_a,', ',field_b,', ',field_c), ', ,', ',')

2. Second possibility: use arrays to concatenate, they concatenate NULL values as empty strings that you can then remove. Use one of this expressions:

replace( array_to_string (array (field_a,field_b,field_c)),',,',',')

Or, alternatively:

array_to_string (array_remove_all (array (field_a,field_b,field_c),''))

enter image description here

• pete: It seems array expressions are the answer to nearly all of my questions :) Soon I will be an array expert. Thanks @Babel.

Answer 2

Another possibility in case you want to concatenate all fields in your attribute table: map_akeys( attributes( )) gets the names of all attributes, array_to_string with delimiter + gets a string like a+b+c that you can than evaluate with eval() so that the actual contents of a, b and c are added:

eval (array_to_string (map_akeys( attributes( )), delimiter:='+'))

enter image description here