Interestingly, I found the database option "concat null yields null" when set to either True or False, it yields null values only.. unless SET CONCAT_NULL_YIELDS_NULL ON is specified..

I could not upload in the comments section, so find them here..

Test script with the option turned ON

Test script with the option turned OFF