Results 1 to 5 of 5

Thread: T-sql

  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Unanswered: T-sql

    update DOJ
    Set full_address = level_floor + ' ' + Unit + ' ' + Street_Number + ' ' + Street_Name + ' ' + Street_postfix + ' ' +
    Postcode + ' ' + Suburb


    i'm trying to update a single field with a number of fields. It is still Null eventhough the update has run without errors

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    hi,

    if any of the fields u r trying to concatenate is NULL, the result string will also be set to NULL. try using ISNULL(fieldname,'') in your UPDATE statement
    Cheers....

    baburajv

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    UPDATE DOJ
       SET full_address = COALESCE(level_floor + ' ','') 
    		+ COALESCE(Unit 	   + ' ','') 
    		+ COALESCE(Street_Number   + ' ','') 
    		+ COALESCE(Street_Name     + ' ','') 
    		+ COALESCE(Street_postfix  + ' ','') 
    		+ COALESCE(Postcode        + ' ','') 
    		+ COALESCE(Suburb,'')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Posts
    50
    Just out of curiosity, is there a performance difference between ISNULL and COALESCE?

    It looks to me like the ISNULL is a simpler function and it should be faster...

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    COALESCE is ANSI, ISNULL is not....

    If you work with many different platforms...staying ANSI makes your life easier.

    As far as being faster...COALESCE does more so it may be. Also, ISNULL is made for the SQL Server platform, so it may be architected to be optimized...don't know...

    I do know you would be hard pressed to find a measurable difference...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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