CODEWITHSUNDEEP
sqlsql-serversql-server-2014
AngryHacker
AngryHacker

Reputation: 61646

How to combine First, Middle, Last names in a single statement?

I need to combine FirstName, MiddleInitial, LastName columns into a DisplayName in a SELECT statement. The problem is that Middle Name is sometimes NULL and sometimes empty.

The data looks like this:

FirstName MiddleInitial LastName
Frank P Rizzo
Saul Rosenberg
Ali NULL Kamal

I am handling NULL in the following manner:

SELECT CONCAT(FirstName + ' ', COALESCE(MiddleInitial + ' ', ''), LastName)
FROM dbo.Person

What I want to get from these rows is Frank P Rizzo, Saul Rosenberg, Ali Kamal.
However, if the MiddleInitial is empty string, I get Saul Rosenberg with 2 spaces.

How can I handle both NULL and empty strings?

Upvotes: 0

Views: 549

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

I think this does what you want:

select ltrim( (case when firstname <> '' then ' ' + firstname else '' end) +
              (case when MiddleInitial <> '' then ' ' + MiddleInitial else '' end) +
              (case when lastname <> '' then ' ' + lastname else '' end)
            )

Upvotes: 1

Thom A
Thom A

Reputation: 96016

If you're on a recent version of SQL Server, you can simply use CONCAT_WS:

SELECT CONCAT_WS(' ',Firstname, MiddleInitial, LastName
FROM dbo.YourTable;

If you're on an older version, you can put the "delimiter" (in this case a space (' ')) before each value, and strip the first one out with STUFF:

SELECT STUFF(CONCAT(' ' + Firstname, ' ' + MiddleInitial, ' ' + LastName),1,1,'')
FROM dbo.YourTable;

If a value might be blank, really you should be fixing your design and have a check constraint that doesn't allow '' values, but you could wrap each value with a NULLIF:

SELECT STUFF(CONCAT(' ' + NULLIF(Firstname,''), ' ' + NULLIF(MiddleInitial,''), ' ' + NULLIF(LastName,'')),1,1,'')
FROM dbo.YourTable;

Upvotes: 2

Dale K
Dale K

Reputation: 27462

Getting the correct spaces in these sort of strings is quite tricky actually. The following should do the job. It combines them 2 at a time and if both are not blank adds a space between them.

SELECT  COALESCE(N.Forename, '') + CASE WHEN COALESCE(N.Forename, '') != ''
                                             AND COALESCE(P.Surname, '') != '' THEN ' '
                                        ELSE ''
                                   END + COALESCE(P.Surname, '')
FROM    dbo.Person
CROSS APPLY ( VALUES ( COALESCE(P.FirstName, '') + CASE WHEN COALESCE(P.FirstName, '') != ''
                                                             AND COALESCE(P.MiddleInitial, '') != '' THEN ' '
                                                        ELSE ''
                                                   END + COALESCE(P.MiddleInitial, '')) ) AS N (Forename)

Upvotes: 1

Related Questions