Post reply

null value for Middle initial for combined name

  • February 23, 2010 at 3:10 pm

    #230298

    I hope this is the right place to ask this. If not I apologize and would request guidance.

    I have this sql statement

    SELECT [Fname]+' '+[middle]+' '+[Lname] AS fullname

    It works just fine and produces John R Smith in the fullname column. However, I do allow nulls for the middle value and when the middle initial is left blank, I don't return any value at all, like the whole record is blank.

    Is my statement flawed or do I need something different in the middle column than just allow nulls? Unfortunately requiring an initial is no an option.

    Thanks very much for any advise.

  • February 23, 2010 at 3:18 pm

    #1123923

    One option is:

    SELECT [Fname] + ' ' + ISNULL([middle] + ' ', '') + [Lname] AS fullname

  • February 23, 2010 at 3:23 pm

    #1123927

    Look at the ISNULL function.

    ISNULL ( check_expression , replacement_value )

    check_expression

    Is the expression to be checked for NULL. check_expression can be of any type.

    replacement_value

    Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion

    SELECT [Fname]+' '+ISNULL([middle],' ')+' '+[Lname] AS fullname

    that is replace the null value with a blank - or use a CASE statement if the blank makes 2 blanks between first and last name and that is not desirable.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 23, 2010 at 3:43 pm

    #1123940

    Also, just as another avenue, you could use: SET CONCAT_NULL_YIELDS_NULL OFF

    That causes concatenation to return a value even when it has a null value as one of the pieces being concatenated.

  • February 26, 2010 at 7:22 am

    #1125567

    Thanks very much to all who responded. This solved my problem. 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply