CODEWITHSUNDEEP
sql-serversql-server-2012coalesceisnull
Zaker
Zaker

Reputation: 537

How to combine FirstName, MiddleName, LastName, comma and suffix in SQL Server?

I am trying to combine FirstName, MiddleName, LastName then a comma and suffix. An example here describes this:

Sno. FirstName  MiddleName  LastName        Suffix    Result
---------------------------------------------------------------
1.   ROBERT     NULL        SMALLWOOD       NULL      ROBERT SMALLWOOD
2.   KIRK       NULL        ROBERTS         MR        KIRK ROBERTS, MR
3.   WILLIAM    DARRELL     WATTENBARGER    Jr.       WILLIAM DARRELL WATTENBARGER, MR

If there is no Suffix, then comma (,) should not be appended also there should be only one space after every column.

So far I tried

REPLACE(RTRIM(Coalesce(FirstName + ' ', '') + Coalesce(MiddleName + ' ', '') + Coalesce(LastName + ' ', '') + Coalesce(SuffixId + ' ', '')),'  ',' ')

Now how should I add a comma before suffix in case if it only exists without using case.

Thanks

Upvotes: 1

Views: 3549

Answers (4)

Sri Reddy
Sri Reddy

Reputation: 7012

I wanted to do something similar and I used COALESCE like OP. Using the suggestion from Mark Adelsberger my query looked like this:

SELECT 
    RTrim(Coalesce(FirstName + ' ', '')
        + Coalesce(MiddleName + ' ', '')
        + Coalesce(LastName + ' ', '')
        + Coalesce(', ' + Suffix, '')) FullName
FROM dbo.YourTable

Upvotes: 0

Drishya1
Drishya1

Reputation: 269

This is the solution I came up with, please try and see if that worked for you.

create table #FullNameCalculation
(
    Sno int not null,
    FirstName varchar(10),
    MiddleName varchar(10),
    LastName varchar(15),
    Suffix varchar(5)
)

insert into #FullNameCalculation
    (Sno,FirstName,MiddleName,LastName,Suffix) 
values
    (1, 'ROBERT', NULL,'SMALLWOOD', NULL),
    (2, 'KIRK', NULL, 'ROBERTS', 'MR'),
    (3,'WILLIAM', 'DARRELL', 'WATTENBARGER', 'JR.'),
    (4,NULL,'BARBER','SINK','MS'),
    (5,NULL,NULL,'SANDERS','MRS.'),
    (6,'SARA',' D','WILLIAMS ',' MS');

SELECT * FROM #FullNameCalculation;

SELECT *, 
    RTRIM(LTRIM(CONCAT(RTRIM(LTRIM(FirstName)),' ',
                       RTRIM(LTRIM(MiddleName)),' ',
                       RTRIM(LTRIM(LastName)),
                       iif(RTRIM(LTRIM(Suffix)) IS NULL OR RTRIM(LTRIM(Suffix))='','',', '+RTRIM(LTRIM(Suffix)))))) AS FullName 
FROM #FullNameCalculation;

Upvotes: 0

Lamak
Lamak

Reputation: 70678

You can use CONCAT:

SELECT CONCAT(FirstName,' ',MiddleName + ' ' ,LastName,', '+NULLIF(Suffix,'')) [Result]
FROM dbo.YourTable;

Upvotes: 3

Richard
Richard

Reputation: 1124

Use ISNULL:

SELECT ISNULL(FirstName + ' ', '') + ISNULL(MiddleName + ' ', '') + ISNULL(LastName + ' ', '') + ISNULL(', ' + SuffixId, '') 
    FROM My_Table_Name

Upvotes: 1

Related Questions