CODEWITHSUNDEEP
sql-serverssms
Neeraj Kumar
Neeraj Kumar

Reputation: 57

How to avoid space while concatenation if database values are null?

I am concatenating three database values Firstname,middlename,lastname.Here is the Query - 

Select ISNULL(t.FirstName,'')+' '+ISNULL(t.MiddleName,'')+' '+ ISNULL(t.LastName,'') as [UserName],Email from table1 t

I am getting null values for some middle names. Thats why I am getting extra spaces after concatenation.

Firstname LastName --getting two spaces at place of one

I want if middle name is null then only one space will be present in result.If it not null then there is one space between firstname, middlename and lastname. I have tried some methods to avoid this but nothing worked.

Upvotes: 2

Views: 1541

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13006

you can use replace() function to replace double spaces to single space

Select replace(concat(coalesce(t.FirstName,''), ' '
    , coalesce(t.MiddleName,' ')
    , coalesce(t.LastName,''), '  ', ' ')) as [UserName]
    , Email 
from table1 t

Upvotes: 2

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Put the space inside ISNULL function:

Select ISNULL(t.FirstName + ' ', '') 
    + ISNULL(t.MiddleName + ' ', '')
    + ISNULL(t.LastName,'') as [UserName]

Upvotes: 6

Yeou
Yeou

Reputation: 632

you can make use of case when clause

SELECT ISNULL(t.FirstName,'') + 
  CASE WHEN t.MiddleName IS NOT NULL THEN
    ' ' + t.MiddleName + ' '
  ELSE 
    ' ' 
  END +
  ISNULL(t.LastName,'') as [UserName],
  Email
FROM table1 t

Example here

Upvotes: 0

Related Questions