CODEWITHSUNDEEP
sqlsql-server
user2307253
user2307253

Reputation:

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.

Upvotes: 0

Views: 5102

Answers (5)

Ritesh Yadav
Ritesh Yadav

Reputation: 321

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

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

Upvotes: 0

GarethD
GarethD

Reputation: 69759

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

Upvotes: 0

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

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

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

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

Upvotes: 1

Related Questions