Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: Select Query & Merge two fields

    Hi Again,

    I am trying to do this Select statment

    PHP Code:

    SELECT 
    (FirstName ', ' SecondName) as 'Customertext' FROM Customers 
    it work ok...but when one of the fields is null it does not work...

    How can I check if one of the fields is null do not merge them instead return the Non Null field as 'Customertext'

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the COALESCE function on them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    5
    Quote Originally Posted by r937 View Post
    use the COALESCE function on them
    would this only return the first non null value???

    I want to merge the two fields if both r not null....

    so
    if
    FirstName = "Name1"
    SecondName = "Name2"

    I want to get "Name1, Name2"


    if
    FirstName = NULL
    SecondName = "Name2"

    I want to get "Name2"


    if
    FirstName = "Name1"
    SecondName = NULL
    I want to get "Name1"

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the COALESCE function on both the firstname and secondname columns individually
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    5
    Quote Originally Posted by r937 View Post
    use the COALESCE function on both the firstname and secondname columns individually
    I tried this

    PHP Code:

    SELECT 
    (COALESCE(FirstName,FirstName) + ', ' COALESCE(SecondName,SecondName)) as 'Customertext' FROM Customers 
    but that is still return null when one of the fields is null

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mmalaka View Post
    COALESCE(FirstName,FirstName)
    that's not a very useful invocation of the COALESCE function

    if firstname is null, you're saying that you want the value of firstname used instead

    i should think that an empty string would be a better choice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rudy is probably walking you there, but I'll jump ingst.
    Your best results would be achieved by bringing the concatenated comma in to the first COALESCE.

  8. #8
    Join Date
    Mar 2010
    Posts
    5
    Quote Originally Posted by r937 View Post
    that's not a very useful invocation of the COALESCE function

    if firstname is null, you're saying that you want the value of firstname used instead

    i should think that an empty string would be a better choice

    ahhh correct....

    worked fine

    Thanks

Posting Permissions

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