Post reply

Concatinating to a NULL column

  • December 12, 2006 at 3:37 pm

    #368561

    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.

  • December 12, 2006 at 3:59 pm

    #677660

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

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

  • December 12, 2006 at 4:02 pm

    #677663

    That's exactly what I was looking for.

    Thanks.

  • December 13, 2006 at 7:36 am

    #677765

    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