Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    Durban - South Africa
    Posts
    13

    Angry Unanswered: NULL+ x = NULL problem

    Hi there,
    Is there any setting in SQL that can disable this silly logic.
    For example, in a number cleaning query I want to combine the Code and the Number

    The problem is that I either the code or the number is null, I lose the number:

    Code:
     
    update tbl
    set Teleh = CodeH + TeleH
    I know that I can split this and check if one is null, both null, both non-null but i am sure there must be an easier way such as Accesses & operator

    The way that I am using now does this, but i'm not sure if this is the best approach
    Code:
     
    update tbl
    set Teleh = isnull(Codeh,'') + isnull(Teleh,'')
    The problem with this is that the result is not stored as null, but as '' length 0
    TrevorW
    If at first you don't succeed, call it version 1.0

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TrevorW
    The problem with this is that the result is not stored as null, but as '' length 0
    but that's exactly what you wanted!!

    or are you saying that if they are both null then you want the result null?

    in that case (pun intended),
    Code:
    update tbl
       set Teleh = case when CodeH is null
                         and TeleH is null
                        then null
                        else coalesce(CodeH,'')
                            +coalesce(TeleH,'')
                         end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Durban - South Africa
    Posts
    13
    Sorry
    Often, the entire number is stored in CodeH and Teleh is empty
    So if a update it to Codeh + Teleh, I loose the correct number in Codeh
    TrevorW
    If at first you don't succeed, call it version 1.0

Posting Permissions

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