CODEWITHSUNDEEP
sqlsql-servert-sql
Rasik
Rasik

Reputation: 2410

How to combine first name, middle name and last name, where middlename is either empty or null

I am trying to concat firstname, lastname and middlename using SQL Server.

SQL I am trying to use is:

SELECT REPLACE(CONCAT_WS(' ',FirstName,MiddleName,LastName),'  ',' ') AS Name
FROM dbo.address

But this query is giving me a weird output in some cases:

Here are the sample data in my use case.

firstname middlename lastname
John NULL Lapp
John Lapp
John Lapp
Accounts NULL Payable
Accounts Payable
Accounts Payable
Accounts NULL Payable

But in the output, there is one additional space in the name when the middlename is empty. Sorry for the screenshot for the output.

enter image description here

But in another case with the same type of data, where middlename is null or empty, it gives the correct output, which means there is no additional space in between the name when the middle name is an empty string.

enter image description here

After trying the answer to the question, I got another weird result.

enter image description here

Upvotes: 0

Views: 2141

Answers (3)

ekochergin
ekochergin

Reputation: 4129

To get started I would use trim function to sanitize the inputs and remove all the possible trailing and leading spaces

select concat_ws(' ', trim(firstname), isnull(trim(middlename), ''), trim(lastname)),
  from src_data;

But trim(' ') returns an empty symbol which is not null and from the docs

"CONCAT_WS ignores null values during concatenation, and does not add the separator between null values."

It means concat_ws will return

source record result
"John - null - Doe" "John - space - Doe"
"John - empty symbol - Doe "John - space+space - Doe"

So, all is left is to convert all the empty symbols to nulls

select concat_ws(' ', trim(firstname), replace(trim(middlename), '', null), trim(lastname))
  from src_data;

I did that for the middle name but, since user input has definitely not been sanitized properly, I'd do that for first- and lastname too

Here's dbfiddle

Upvotes: 0

Himanshu Aggarwal
Himanshu Aggarwal

Reputation: 227

select FName+' '+ IsNull(NullIf(MName + ' ',''),'') + LName
from tblName

enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

The CONCAT_WS function is working its magic. The problem is that you either have empty string or single space for some of the middle name values, in certain records. Rightfully, the missing middle names should always be NULL. Here is one workaround:

SELECT
    CONCAT_WS(' ', FirstName,
                   CASE WHEN MiddleName NOT IN ('', ' ') THEN MiddleName END,
                   LastName) AS Name
FROM dbo.address;

The logic here is to replace an empty string or single space middle name with NULL, thereby allowing CONCAT_WS to ignore it.

Upvotes: 4

Related Questions