Does concatenation return NULL when one of the involved fields is NULL ?

I have a database on SQL SERVER 2000 in which I am trying to write a trigger on updates of the table tblMMMaterials.

When that table is updated, I want one of two things to happen:

1. If fields Product_Description, Product_Specification, Product_Design, Product_Color and [Unit:] are all NULL, Assign to field MaterialDescription the value of field MaterialDescriptionBACKUP.
2. If one or more of the aforementionned fields are NOT NULL (have a value), assign to field MaterialDescription the value of the concatenation of the aforementionned fields.

Here is the code I have developped:

CREATE TRIGGER tblMMMaterials_UTrig ON dbo.tblMMMaterials FOR UPDATE AS

IF (UPDATE(Product_Description)
OR UPDATE(Product_Specification)
OR UPDATE(Product_Design)
OR UPDATE(Product_Color)
OR UPDATE([Unit:])
OR UPDATE(MaterialDescriptionBACKUP))
BEGIN
UPDATE tblMMMaterials
SET MaterialDescription = MaterialDescriptionBACKUP
WHERE MaterialID IN (SELECT MaterialID FROM inserted)
AND Product_Description IS NULL
AND Product_Specification IS NULL
AND Product_Design IS NULL
AND Product_Color IS NULL
AND [Unit:] IS NULL

UPDATE tblMMMaterials
SET MaterialDescription = Product_Description + ' ' + Product_Specification + ' ' + Product_Design + ' ' + Product_Color + ' (' + [Unit:] + ')'
WHERE MaterialID IN (SELECT MaterialID FROM inserted)
AND (Product_Description IS NOT NULL
OR Product_Specification IS NOT NULL
OR Product_Design IS NOT NULL
OR Product_Color IS NOT NULL
OR [Unit:] IS NOT NULL)
END

When the 5 fields are null, the value of MaterialDescriptionBACKUP is assigned to field MaterialDescription, as expected.
When all 5 fields have values, the concatenation of them is assigned as value for field MaterialDescription, as expected.
But when 1 or more field is null AND 1 or more field has a value, MaterialDescription takes the value NULL.

I believe this may be because the concatenation fails when one or more fields are null and returns null. Am I correct? If so, how can I work around this?

Well, you *are* asking in a MySQL forum, and each DB is (or can) be different in this case.

But why didn't you just *TRY* it to find out for sure?

(Using SQL Server 2008, by the by.)


use testdb

select * from ldummy

n1 n2
one two
two NULL
NULL three
NULL NULL

select n1 + n2 AS both from dummy

both
onetwo
NULL
NULL
NULL

How hard was that? Yep, concatenation with a null produces null.

Now try

select ISNULL(n1,'[blank]') + ISNULL(n2,'[blank]') AS both from dummy

both
onetwo
two[blank]
[blank]three
[blank][blank]


(And to keep this relevant to MySQL: The function is named IFNULL( ) in MySQL, but otherwise works the same.

And, yes, you can use
select ISNULL(n1,'') + ISNULL(n2,'') AS both from dummy

to get a blank string in place of the null.