How to remove the whitespace while concatenating while some have Null values?

Question

I am trying to concatenate the name of the players where some of the players have no middle name. While concatenating as below I am getting an white space for players without a middle name and logic holds good for players with a middle name. How do I remove the unwanted whitespace for NULL valued columns alone?

I want only the Initial of the middle name in the concatenate expression.

SELECT m_playerid, first_name + ' ' + SUBSTRING (coalesce (middle_
name, ' '), 1,1) + ' ' + last_name as [Full name]
, game as Game, inns as Innings, [scores] as Scores FROM  odsports

Shouldn't I be introducing a condition to get remove of the whitespace for NULL? I am struck!

Answer 1

You can use the fact that concatenating a NULL to anything with the + operator produces a NULL whereas the CONCAT function converts NULL to empty string.

So CONCAT(first_name, ' ', LEFT(middle_name,1) + ' ', last_name) will handle null middle names as you want - as in the following example

WITH T(first_name, middle_name, last_name) AS
(
SELECT 'Franklin', 'Delano', 'Roosevelt' union all
SELECT 'Barack', NULL, 'Obama'
)
SELECT CONCAT(first_name, ' ', LEFT(middle_name,1) + ' ', last_name)
FROM T

Returns

+----------------------+
|   (No column name)   |
+----------------------+
| Franklin D Roosevelt |
| Barack Obama         |
+----------------------+