Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    31

    Unanswered: String concatenation

    Sorry i am posting this question in here but since my code was in a trigger i thought it might give me some ideas.

    This is my code

    UPDATE sfs.dbo.SF_MESSAGE

    SET FIELD_9 = @bill_firstname + @bill_lastname,
    FIELD_13 = @bill_add1 + @bill_add2

    this code works if there are fields present for both @bill_add1 and @bill_add2 in the database i.e none of them are NULL.
    But if either of them r NULL, this code doesn't work.
    How can i modify such that even there are null values for either field there should still be an output?

    Thanks in advance..

    Gayathri

  2. #2
    Join Date
    Nov 2003
    Posts
    31
    hi again

    Sorry i just found out the ans to my qn. I just had to add in the code below and it works the way i want.

    SET CONCAT_NULL_YIELDS_NULL OFF

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "SET CONCAT_NULL_YIELDS_NULL OFF" will work, but it is intended to be used as a connection setting. It would be better coding practice to write your statement like this:

    UPDATE sfs.dbo.SF_MESSAGE

    SET FIELD_9 = isnull(@bill_firstname, '') + isnull(@bill_lastname, ''),
    FIELD_13 = isnull(@bill_add1, '') + isnull(@bill_add2, '')

    ...which works no matter what the connection setting or permissions of the person executing the statement.

    blindman

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I would definately go along with Blindman on this one, his example is probably as close to perfect as you are going to get.

  5. #5
    Join Date
    Nov 2003
    Posts
    31
    hi thanks for the alternative solution. I just tried and it does work that way too....u guys r great!

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    technically your other one is working....

    but what happens when you add something to null? well, since null is undefined you can determine what you get so you get something that is undefined eg. null.

    it's like the whole is null vs = null thing

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Just know that data manipulation with a value that is potentially null might result in unexpected behavior.

    Check out bol when in doubt. The following is an excerpt from "string concatention":

    Remarks
    When you concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL. With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, 'string' + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is 'string'.

Posting Permissions

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