CODEWITHSUNDEEP
sql-serversql-server-2016string-concatenation
C82
C82

Reputation: 61

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

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!

Upvotes: 3

Views: 2360

Answers (4)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Add a replace for double spaces, as well as use isnull function. Try this

SELECT 
m_playerid, 
REPLACE(
LTRIM(RTRIM(ISNULL(first_name ,'')))
+CASE WHEN middle_name IS NULL
        THEN ' '
    ELSE ' '+LEFT(ISNULL(middle_name,' '),1)+' ' END
+
LTRIM(RTRIM(ISNULL(last_name,'')))
,'  ',' ') as [Full name],
game as Game, 
inns as Innings, 
[scores] as Scores 
FROM  odsports

Upvotes: 1

santoshkumar Pithani
santoshkumar Pithani

Reputation: 121

SELECT 
   m_playerid, 
   LTRIM(CONCAT(first_name,Space(1),LTRIM(RTRIM(middle_name+space(1)+last_name)))) 
                                                                         as [Full name],
   game as Game,
   inns as Innings, 
   [scores] as Scores 
FROM  odsports

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453608

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

Upvotes: 3

Mohammad Bafkar
Mohammad Bafkar

Reputation: 1

Try this:

SELECT  m_playerid,
    COALESCE(first_name + ' ' + middle_name + ' ' + last_name,
             first_name + ' ' + last_name,
             first_name,
             last_name) as [Full name],
    game as Game, 
    inns as Innings, 
    [scores] as Scores
FROM odsports

Upvotes: 0

Related Questions