Web Analytics Made Easy -
StatCounter Does concatenation return NULL when one of the involved fields is NULL ? - CodingForum

Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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:
    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; Aug 27, 2011, 10:35 PM. Reason: corrected typo
    Shawn

  • #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.)

    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.
    Be yourself. No one else is as qualified.

    Comment

    Working...
    X