Search code examples
sql-serverconcatenationvarcharspaces

Correct Amount of Spaces with concatenated person's name in SQL Server

Here is my SQL as I have it now. The first and last name should never be missing because they are required on the interface with validation. However, if the middle initial is missing, I don't want two spaces in the result; I just want one space. I could put a case statement in, but that seems like overkill if SQL already has a function for this purpose. Does anyone know if there is a function for this purpose? What would the code look like?

SELECT ISNULL(contact.firstname, '') 
         + ' ' 
         + ISNULL(contact.middleinitial, '') 
         + ' ' 
         + ISNULL(contact.lastname, '')
FROM dbo.contact
Solution 
  • SELECT
 ISNULL(contact.firstname, '') + 
 ISNULL(' ' + contact.middleinitial + ' ', ' ') + 
 ISNULL(contact.lastname, '')

    However, you either should remove ISNULL for firstname, lastname (defined NOT NULL?) or add some trims

    SELECT
 LTRIM(RTRIM(
   ISNULL(contact.firstname, '') + 
    ISNULL(' ' + contact.middleinitial + ' ', ' ') + 
    ISNULL(contact.lastname, '')
 ))