CODEWITHSUNDEEP
sqlsql-servert-sql
Paul
Paul

Reputation: 1159

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

I'm trying not to reinvent the wheel 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.

Upvotes: 11

Views: 43867

Answers (10)

Nic
Nic

Reputation: 703

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.

Upvotes: 0

Bishop Chakraborty
Bishop Chakraborty

Reputation: 1

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

Upvotes: -2

Harikumar
Harikumar

Reputation: 1

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

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176234

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

Upvotes: 1

Vijred
Vijred

Reputation: 379

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,'')))+'|'
--

Upvotes: 1

Akshay Kumar Konduri
Akshay Kumar Konduri

Reputation: 1

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

Upvotes: -1

Jonathan
Jonathan

Reputation: 1719

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

Upvotes: 0

Daniel Vassallo
Daniel Vassallo

Reputation: 344551

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

Upvotes: 9

marc_s
marc_s

Reputation: 755451

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

Upvotes: 13

Thomas
Thomas

Reputation: 64674

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.

Upvotes: 27

Related Questions