Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Senior Coder
    Join Date
    Apr 2003
    Location
    Canada
    Posts
    1,063
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question 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:
    Code:
    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?
    Last edited by shlagish; 08-28-2011 at 02:35 AM. Reason: corrected typo
    Shawn

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,585
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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.)

    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
    How hard was that? Yep, concatenation with a null produces null.

    Now try
    Code:
    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
    Code:
    select ISNULL(n1,'') + ISNULL(n2,'') AS both from dummy
    to get a blank string in place of the null.
    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.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •