How to replace column if not null in select statement?

Question

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

score 6 · Answer 1 · edited Apr 24 '16 at 14:21

6

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;

edited Apr 24 '16 at 14:21

Paul White

94,921
30
437
687

answered Apr 21 '16 at 15:36

Shaneis

1,803
2
15
20

score 3 · Answer 2 · edited Apr 13 '17 at 12:42

My personal preference is to use COALESCE as it is ANSI standard.

CREATE TABLE dbo.Contact
    ([ID] int, [FirstName] varchar(5), [LastName] varchar(6), [MiddleName] varchar(4))
;

INSERT INTO dbo.Contact
    ([ID], [FirstName], [LastName], [MiddleName])
VALUES
    (1, 'Mark', 'Jordan', 'E'),
    (2, 'James', 'Blunt', NULL)
;

--- below both tsql will give you the desired results

set nocount on;
SELECT LTRIM(RTRIM(COALESCE(FirstName, '') +' '+ COALESCE((MiddleName + '. '), ' ') + COALESCE(LastName, '')))
FROM dbo.Contact

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

Mark E. Jordan

James Blunt

Also, just for completeness refer to If a person's name is Null then how would it break the database?

score 0 · Answer 3 · answered Apr 21 '16 at 15:39

You can always use CASE to get that reverse logic, but it's easier than that in your case.

--With names in separate columns
SELECT FirstName, ISNULL((MiddleName + '.'), '') AS MiddleName, LastName
FROM Contact

--With names merged together, removing any leading/trailing spaces.
SELECT LTRIM(RTRIM(ISNULL(FirstName, '') + ISNULL((MiddleName + '. '), ' ') + ISNULL(LastName, '')))
FROM Contact

How to replace column if not null in select statement?

3 Answers3