Kapil Kothari Kapil Kothari - 1 month ago 8
SQL Question

Concatenate first name, last name and middle name with comma

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.

Answer

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
...