How to replace column if not null in select statement? I have a Contact (ID, FirstName, LastName, MiddleName).

For example:

| ID | FirstName | LastName | MiddleName |
|====|===========|==========|============|
| 1  | Mark      |Jordan    | E          |
|----|-----------|----------|------------|
| 2  | James     | Blunt    | NULL       | 
|----|-----------|----------|------------|


I want to output 

Mark E. Jordan
James Blunt


So, if MiddleName is not null, add a '.' after, otherwise output null.

I am aware of isnull function, however what I need is an isnotnull function. How can I finish the query?

My query so far:

select FirstName + ' ' + isnull(...)  + LastName
from Contact
It's great that a NULL plus a value produces a NULL*, means you can do stuff like this...

SELECT FirstName + ' ' + ISNULL(MiddleName + '. ', '') + LastName 
FROM Contact;


Hopefully, this works for what you want.

* Assuming CONCAT_NULL_YIELDS_NULL is set ON, as it should be;