Handling NULL values in labeling? [duplicate]

Question

In QGIS (2.10.1 on a Mac), I am trying to make some multiline labels on a map I have with a bunch of trees in them. In the attribute table I have columns with the Latin scientific name, the English name (when one exists), the year the tree was planted etc.

I am labelling the trees in my map with an expression like this:

genus_species + '\n' +
case
    when  "english_species" is not null then  "english_species" + '\n'
end
+ to_string( "planted_year")

This works well for the rows where english_species is not null, however whenever I have a row with no English name, the entire expression just seems to break and nothing is printed, not even the scientific name or the year (which always exists).

I have tried adding a

when  "english_species" is null then ''

But that doesn't work either. The expression just breaks for rows with no value in the field english_species.

Answer 1

So the way QGIS handles NULL when you join strings is that it will convert the result to NULL if any part is NULL (yes I know it's on going debate on if it should or not)

Here is how you handle that using the concat function:

concat( "genus_species", '\n', "english_species", '\n', to_string("planted_year")

The concat function will convert NULL to empty string and join anyway.

Answer 2

You should use string concatenation instead of plus ( symbol ||). It should look more like this:

 CASE
    WHEN "english_species" IS NULL THEN "genus_species" || '\n' || to_string("planted_year")
    ELSE  "genus_species" || '\n' || "english_species" || '\n' || to_string("planted_year")
 END

Update, in 2.10 it is not needed to use || for strings anymore so it is all about need of using ELSE, otherwise the expression is not valid because you end up with + + if it is not the case. So other possible option:

 "genus_species" + '\n' +
 CASE
    WHEN "english_species" IS NOT NULL THEN "english_species" + '\n'
    ELSE '' 
 END
 + to_string("planted_year")

Which in other case makes + '' + and though is valid.