1

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
5

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;

3

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?

0

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

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.