Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: Update query with NULL fields

    Hi,

    I'm trying to use an update query which concats two fields into one eg. a + b = ab.

    If one of the fields is empty (null value) the result is also empty (null).

    I've set the CONCAT_NULL_YIELDS_NULL to off and on, but I see no difference.

    Do I need to set anyting else some other parameter or anything to make this work.

    Thx.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use COALESCE to convert nulls to empty strings

    update yourtable
    set foo = coalesce(a,'')+coalesce(b,'')

    note that null and empty are not the same thing at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Setting CONCAT_NULL_YIELDS_NULL to OFF should work:

    -------------------------------------------------
    SET CONCAT_NULL_YIELDS_NULL OFF

    declare @fielda varchar(20)
    declare @fieldb varchar(20)

    set @fieldb = 'test'

    select @fielda + @fieldb
    --------------------------------------------------


    ...though I think r937's suggestion to use coalesce (or isnull()) is better programming practice.

    blindman

  4. #4
    Join Date
    Oct 2003
    Posts
    6
    Thanks!! COALESCE works for me.

Posting Permissions

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