Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
08-28-2011, 02:33 AM #1
- Join Date
- Apr 2003
- Thanked 0 Times in 0 Posts
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 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?
Last edited by shlagish; 08-28-2011 at 02:35 AM. Reason: corrected typoShawn
08-28-2011, 05:23 AM #2
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.)
How hard was that? Yep, concatenation with a null produces null.Code: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
(And to keep this relevant to MySQL: The function is named IFNULL( ) in MySQL, but otherwise works the same.Code:select ISNULL(n1,'[blank]') + ISNULL(n2,'[blank]') AS both from dummy both onetwo two[blank] [blank]three [blank][blank]
And, yes, you can useto get a blank string in place of the null.Code:select ISNULL(n1,'') + ISNULL(n2,'') AS both from dummy
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.