Concatinating to a NULL column

  • update shop set TasksCompleted = TasksCompleted+'3,' where ShopNo = '25'

    The above code works just fine a long as TasksCompleted is not NULL. However, by default the column is NULL. (I couldn't figure out how to change the default value to "blank" instead of NULL)

    When TasksCompleted is NULL, the code returns 1 row updated but doesn't do anything.

    Any suggestions on how to make this work.

  • Try update shop set TasksCompleted = isnull(TasksCompleted,'')+'3,' where ShopNo = '25'

    I'm sure there are other ways to do this as well.

  • That's exactly what I was looking for.

    Thanks.

  • Also, this way from BOL:

    SET CONCAT_NULL_YIELDS_NULL

    Controls whether or not concatenation results are treated as null or empty string values.

    Syntax

    SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

    Remarks

    When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply