ISNULL Concatentation

If you've got NULLs in there, they are going to get you.....

We've got ten records in the Customers table, yet only three of them have middle names.  How to return all customer names in one select, with and without middle names?  There are many ways to work around NULLs, but here is a quick trick using ISNULL, which replaces NULL with the value you provide (per BOL).

For records with MiddleNames, the full name is returned like this:           Judy Lynn Smith
For records without MiddleNames, the record is returned like this:           Karen Williams


SELECT
REPLACE(FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName, ' ', ' ')  FullName
FROM
dbo.Customers


    FullName
    Jamie Lynn Smith
    Judy Lynn Harris
    Karen  Williams
    Marge  West
    Betsy  Simpson
    Morgan  Best
    Rhianna  Grant
    John Matthew Shaw
    Sammy  Thompson
    Caroline  Pratt