Combine First, Middle Initial, Last name and Suffix in T-SQL (No extra spaces)

I'm trying not to reinvent the wheel开发者_StackOverflow here...I have these four fields:

[tbl_Contacts].[FirstName],
[tbl_Contacts].[MiddleInitial],
[tbl_Contacts].[LastName],
[tbl_Contacts].[Suffix] 

And I want to create a FullName field in a view, but I can't have extra spaces if fields are blank...

So I can't do FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix... Because if there is no middle initial or suffix I'd have 2 extra spaces in the field. I think I need a Case statement, but I thought someone would have a handy method for this...Also, the middleinitial and suffix may be null.

Assuming that all columns could be nullable, you can do something like:

RTrim(Coalesce(FirstName + ' ','') 
+ Coalesce(MiddleInitial + ' ', '')
+ Coalesce(LastName + ' ', '')
+ Coalesce(Suffix, ''))

This relies on the fact that adding to a NULL value yields a NULL.

Whichever options you choose, here's something to think about: this will be a rather involved and thus time consuming option, especially if you have it in a view which gets evaluated each and every time for each and every row in question.

If you need this frequently, I would recommend you add this to your base table as a persisted, computed field - something like:

ALTER TABLE dbo.tbl_Contacts
    ADD FullName AS  (insert the statement of your choice here) PERSISTED

When it's persisted, it becomes part of the underlying table, and it's stored and kept up to date by SQL Server. When you query it, you get back the current value without incurring the cost of having to concatenate together the fields and determine which to use and which to ignore...

Just something to think about - something that too many DBA's and database devs tend to ignore and/or not know about....

You may want to pass the FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix concatenation through the REPLACE() function in order to substitute duplicate spaces into a single space.

REPLACE(FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix, '  ', ' ')
--                                                                        --    -

EDIT:

Just noticed that some of your fields may be NULL, and therefore the above would not work in that case, as the whole string would become NULL. In this case, you can use the COALESCE() method as suggested by Thomas, but still wrapped it in a REPLACE():

REPLACE(RTRIM(COALESCE(FirstName + ' ', '') +
              COALESCE(MiddleInitial + ' ', '') +
              COALESCE(LastName + ' ', '') +
              COALESCE(Suffix, '')), '  ', ' ')

Test:

SELECT REPLACE(RTRIM(COALESCE('John' + ' ', '') +
                     COALESCE('' + ' ', '') +
                     COALESCE('Doe' + ' ', '') +
                     COALESCE(NULL, '')), '  ', ' ')

-- Returns: John Doe

I had to join Firstname, Middlename, and Lastname. my challenge was to handle NULL values, used following code.

RTRIM(LTRIM(RTRIM(isnull(@firstname,'') + ' ' + isnull(@middlename,'')) + ' ' + isnull(@lastname,'')))

Test different scenarios if you are interested :)

DECLARE @firstname VARCHAR(MAX)
DECLARE @middlename VARCHAR(MAX)
DECLARE @lastname VARCHAR(MAX)

set @firstname = 'FirstName'
set @middlename = NULL
set @lastname = 'LastName'

SELECT '|'+RTRIM(LTRIM(RTRIM(isnull(@firstname,'') + ' ' + isnull(@middlename,'')) + ' ' + isnull(@lastname,'')))+'|'
--

If you are using SQL Server 2012+ you could use CONCAT and +:

SELECT RTRIM(
       CONCAT(FirstName + ' ', MiddleInitial + ' ', LastName + ' ', Suffix)
      ) AS [FullName]
FROM tbl_Contacts;

How it works:

1. If any part of full name is NULL then NULL + ' ' → NULL
2. CONCAT handles NULL
3. In case that after part of name there are only NULLs, TRIM last space.

LiveDemo

Here is a solution:

CREATE FUNCTION dbo.udf_IsNullOrEmpty
(
@vchCheckValue VARCHAR(MAX)
,@vchTrueValue VARCHAR(MAX)
,@vchFalseValue VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

RETURN CASE WHEN NULLIF(RTRIM(LTRIM(@vchCheckValue)),'') IS NULL THEN @vchTrueValue ELSE @vchFalseValue END

END

SELECT FirstName + ' ' + 
       dbo.udf_IsNullOrEmpty(MiddleInitial,'',MiddleInitial + ' ') + 
       LastName + 
       dbo.udf_IsNullOrEmpty(Suffix,'',' ' + Suffix)
FROM tbl_Contacts

select CONCAT(IFNULL(FirstName, ''), '', IFNULL(MiddleName, ''), '', IFNULL(LastName, '')) AS name from table

Why not like this:

select concat(fName,' ', 
    case length(mName) 
    when 0 then '' 
    else concat(mName, ' ') end, lName) as fullName

My columns are not null, so this works for me.

create function getfname(@n varchar(30)) returns varchar(30) as begin declare @s varchar(30) set @s=LEFT(@n,charindex(' ',@n)-1) return @s end

create function getLname(@n varchar(30)) returns varchar(30) as begin declare @s varchar(30)

set @s=substring(@n,charindex(' ',@n+1),Len(@n))

return @s end

the query:

SELECT retire.employeehrmsid, 
       Isnull(retire.firstname, '') + ' ' 
       + Isnull(retire.middlename, '') + ' ' 
       + Isnull(retire.lastname, '') AS FullName, 
       retire.dojtoservice, 
       retire.designation, 
       emphistory.currentdoj, 
       emphistory.presentddo, 
       emphistory.office, 
       transfer.generatetid          AS TransferID, 
       transfer.transferdate, 
       transfer.currentlocation, 
       transfer.newlocation, 
       transfer.datas                AS Transfer_Doc, 
       release.generaterid           AS ReleaseID, 
       release.releasedate, 
       release.datar                 AS Release_Doc, 
       employeeserviceupdate.dataeu  AS Join_Doc 
FROM   retire 
       INNER JOIN emphistory 
               ON retire.id = emphistory.id 
       INNER JOIN employeeserviceupdate 
               ON retire.id = employeeserviceupdate.id 
       INNER JOIN transfer 
               ON retire.id = transfer.id 
                  AND emphistory.ehrid = transfer.ehrid 
       INNER JOIN release 
               ON transfer.tid = release.tid