Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Location
    Pullman, WA
    Posts
    4

    Unanswered: How do you concatinate when one value is Null?

    I am trying to combine 2 fields in a SQL statement as follows:

    Select Field1 + Field2 AS tablefields from table

    However when Field2 is Null it makes the column tablefields null regardless of whether Field1 has data or not. Is there a way to avoid this?

    Note*
    The real SQL statement is a series of LEFT OUTER JOINS combining fields from different tables into one field, but if one of the fields is Null it makes the entire end result for that column Null.

    Travis

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello Travis,

    every operation with NULL comes to NULL

    a + NULL = NULL
    a || NULL = NULL and so on and so on

    What you are seeking for is DECODE.

    Try your statement with the following commands


    Select Field1 || DECODE(Field2, NULL, '', Field2) AS tablefields from table


    I hope that you are using Oracle otherwise found the counterpart to DECODE ...

    Hope this helps ?

    Regards

    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Apr 2002
    Location
    Pullman, WA
    Posts
    4
    In this instance I am using SQL Server 2000 which doesn't have DECODE. I figured it out though, have to use the CASE Function like such:

    Select (CASE WHEN Field1 IS NULL THEN '' ELSE Field1) +
    (CASE WHEN Field2 IS NULL THEN '' ELSE Field2 END) As bothfields
    From Table

    Thanks for the lead.
    Am I posting this on the write Forum? Is this only for Oracle/PSQL?

    Travis

    Originally posted by alligatorsql.com
    Hello Travis,

    every operation with NULL comes to NULL

    a + NULL = NULL
    a || NULL = NULL and so on and so on

    What you are seeking for is DECODE.

    Try your statement with the following commands


    Select Field1 || DECODE(Field2, NULL, '', Field2) AS tablefields from table


    I hope that you are using Oracle otherwise found the counterpart to DECODE ...

    Hope this helps ?

    Regards

    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello Travis,

    it is Ok to post SQL questions into this forum, but it is very helpful, when you give a short hint about the database you use. If the question is to specialized, I will move the thread to the correct forum and send an email to the poster

    Regards
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

Posting Permissions

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