Concatenate first name, last name and middle name with comma

Question

I want to concatenate 3 columns in SQL server as below:

MAX(LTRIM(RTRIM((ISNULL(LastName,'') + 
    ', ' + 
    ISNULL(FirstName,'') + 
    ', ' + 
    ISNULL(MiddleName,''))))) AS FullName

I have used value of this column in SELECT clause as:

MAX(FullName) AS FullName,

I would like to handle NULL values, in case all 3 last name, middle name and first name are BLANK or NULL. The query used above will show " , , " in case all 3 columns are NULL or BLANK. But I want to show "N/A" in such case.

Thanks in advance.

http://stackoverflow.com/questions/23836259/what-is-sql-server-function-to-concatenate-with-separator — Dmitry Bychenko, Oct 27 '16 at 09:38
BTW - you could sort out the commas if you needed, like if someone has null middle name, you could avoid John, , Smith appearing - if you wanted — Cato, Oct 27 '16 at 10:26

score 1 · Accepted Answer · answered Oct 27 '16 at 09:41

You could use a CASE expression:

SELECT MAX(CASE WHEN ISNULL(FirstName, '') = '' AND
                     ISNULL(MiddleName, '') = '' AND
                     ISNULL(LastName, '') = ''
                THEN 'N/A'
                ELSE LTRIM(RTRIM((ISNULL(LastName,'') +  ', ' +
                                  ISNULL(FirstName,'') + ', ' +
                                  ISNULL(MiddleName,''))))
           END) AS FullName
FROM yourTable
...

Tharunkumar Reddy · Answer 2 · 2016-10-27T09:45:04.137

0

Use Concat like below this will do implicit conversion. So no need to use ISNULL.

select isnull(MAX(LTRIM(RTRIM((concat(LastName, 
    ', ' ,
    FirstName, 
    ', ' ,
    MiddleName,''))))) ,'n/a')AS FullName from table

edited Oct 27 '16 at 09:45

answered Oct 27 '16 at 09:39

Tharunkumar Reddy

2,773
18
32

score 0 · Answer 3 · answered Oct 27 '16 at 09:43

Check with COALESCE and then CASE Statement:

Declare @FirstName VARCHAR(50)='',@MiddleName VARCHAR(50),@LastName VARCHAR(50)

SELECT 
CASE WHEN ISNULL(COALESCE(@FirstName,@MiddleName,@LastName),'')<>'' 
THEN ISNULL(@FirstName,'')+',' +ISNULL(@MiddleName,'')+','+ISNULL(@LastName,'')
ELSE 'N/A' END AS FullName

score 0 · Answer 4 · edited May 23 '17 at 12:17

The below method may seem quite complicated, but it does make adding or removing columns much simpler, and for all its perceived complexity it isn't actually doing that much under the hood, so doesn't add much overhead.

The first step is to unpivot each of your columns to rows with a common column name, so you would turn

FirstName   MiddleName  LastName    
------------------------------------
A           NULL        C

Into

Name
------
A
NULL
C

Using CROSS APPLY along with the table value constructor VALUES

SELECT  x.Name
FROM    (VALUES ('A', NULL,'C')) AS t (FirstName, MiddleName, LastName) 
CROSS APPLY (VALUES (1, t.FirstName), (2, t.MiddleName), (3, t.LastName)) x (SortOrder, Name)
ORDER BY x.SortOrder

Then you can remove NULLs and blanks with WHERE ISNULL(Name, '') <> '', then you only have valid data to concatenate together which you can do using SQL Server's XML Extensions. So you end up with a full query like:

WITH TestData AS
(   SELECT  *
    FROM (VALUES ('A'), (NULL)) AS f (FirstName)
    CROSS JOIN (VALUES ('B'), (NULL)) AS m (MiddleName)
    CROSS JOIN (VALUES ('C'), (NULL)) AS l (LastName)
)
SELECT  t.*,
        NamesConcat = ISNULL(STUFF(NamesConcat.value('.', 'NVARCHAR(MAX)'), 1, 2, ''), 'N/A')
FROM    TestData AS t
        CROSS APPLY
        (   SELECT  ', ' + x.Name
            FROM    (VALUES 
                        (1, t.FirstName), 
                        (2, t.MiddleName), 
                        (3, t.LastName)
                    ) x (SortOrder, Name)
            WHERE   ISNULL(x.Name, '') <> '' -- NOT BLANK OR NULL
            ORDER BY x.SortOrder
            FOR XML PATH(''), TYPE
        ) x (NamesConcat);

Result

FirstName   MiddleName  LastName    NamesConcat
-------------------------------------------------
A           B           C           A, B, C
A           NULL        C           A, C
A           B           NULL        A, B
A           NULL        NULL        A
NULL        B           C           B, C
NULL        NULL        C           C
NULL        B           NULL        B
NULL        NULL        NULL        N/A

score 0 · Answer 5 · answered Feb 02 '18 at 06:58

select Isnull(FirstName,' ') +' '+ ','+ Isnull(MiddleName,' ')+' '+ ' ,'+ Isnull(Lastname,' ') as Name from personaldata
select FirstName +' '+','+ MiddleName +' '+',' + Lastname as Name from personaldata

Note: The Second query will work fine if all value present and if anyone is null then it will return null for Name, to avoid such kind of concern please use the first query.

Concatenate first name, last name and middle name with comma

5 Answers5