locked
Returns a null RRS feed

  • Question

  • All

    Unwanted nulls are being returned.

    SELECT C1000000018 + ', ' +C1000000019 AS 'Customer' FROM

    T1916   This returns LastName, FirstName  This is OK.

    However, I need to return a Middle Initial if it exists so I tried this

    SELECT C1000000018

    + ', ' + C1000000019 + ' ' + C1000000020 AS 'Customer' FROM

    T1916

    If a middle initial exists the return is Jones, Tom J and this is OK.

    The Problem:

    However, if there if is no middle initial a NULL is returned even though there is always a last and first name.

    How can I write the select to return the last name and first name if there is no middle initial?

    This is what the unwanted return looks like.
    NULL
    NULL
    NULL
    NULL
    Smith, David T
    Smith, Kim D
    Brown, Peter N.
    NULL
    NULL

    Many thanks

    Pete

     

    Wednesday, November 16, 2011 2:23 PM

Answers

  • Try:

    SELECT C1000000018
    
    + ', ' + C1000000019 + COALESCE(' ' + C1000000020,'') AS 'Customer' FROM
    

    I'm using COALESCE function for the last column (middle initial) to convert NULL into empty string.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Wednesday, November 16, 2011 2:33 PM
    • Marked as answer by remdev59 Wednesday, November 16, 2011 2:58 PM
    Wednesday, November 16, 2011 2:24 PM
  • You may need to guard against NULLs for the subsequent appends something like below:

    SELECT C1000000018 + ', ' + ISNULL(C1000000019, '') + ' ' + ISNULL(C1000000020, '') AS 'Customer' 
    
    FROM T1916
    
    

    • Proposed as answer by Hasham NiazEditor Wednesday, November 16, 2011 2:29 PM
    • Marked as answer by remdev59 Wednesday, November 16, 2011 2:58 PM
    Wednesday, November 16, 2011 2:27 PM
  • HI Pete !

    You may also use the below query to get your desired output;

    SELECT ISNULL(C1000000018,'') + ', ' + ISNULL(C1000000019,'') + ' ' + ISNULL(C1000000020,'') AS 'Customer' FROM T1916
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

     

    • Marked as answer by remdev59 Wednesday, November 16, 2011 2:58 PM
    Wednesday, November 16, 2011 2:31 PM
    Answerer

All replies

  • Try:

    SELECT C1000000018
    
    + ', ' + C1000000019 + COALESCE(' ' + C1000000020,'') AS 'Customer' FROM
    

    I'm using COALESCE function for the last column (middle initial) to convert NULL into empty string.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Wednesday, November 16, 2011 2:33 PM
    • Marked as answer by remdev59 Wednesday, November 16, 2011 2:58 PM
    Wednesday, November 16, 2011 2:24 PM
  • You may need to guard against NULLs for the subsequent appends something like below:

    SELECT C1000000018 + ', ' + ISNULL(C1000000019, '') + ' ' + ISNULL(C1000000020, '') AS 'Customer' 
    
    FROM T1916
    
    

    • Proposed as answer by Hasham NiazEditor Wednesday, November 16, 2011 2:29 PM
    • Marked as answer by remdev59 Wednesday, November 16, 2011 2:58 PM
    Wednesday, November 16, 2011 2:27 PM
  • HI Pete !

    You may also use the below query to get your desired output;

    SELECT ISNULL(C1000000018,'') + ', ' + ISNULL(C1000000019,'') + ' ' + ISNULL(C1000000020,'') AS 'Customer' FROM T1916
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

     

    • Marked as answer by remdev59 Wednesday, November 16, 2011 2:58 PM
    Wednesday, November 16, 2011 2:31 PM
    Answerer
  • Tried them all and they all worked!!

    Big thanks to all!!

    Wednesday, November 16, 2011 2:58 PM