CODEWITHSUNDEEP
sql-server
Ritesh Yadav
Ritesh Yadav

Reputation: 321

How to combine first name, middle name and last name in SQL server

you can refer the below queries to get the same-

1

select FirstName +' '+ MiddleName +' ' + Lastname as Name from TableName.

2

select CONCAT(FirstName , ' ' , MiddleName , ' ' , Lastname) as Name from 
  TableName

3

select Isnull(FirstName,' ') +' '+ Isnull(MiddleName,' ')+' '+ Isnull(Lastname,' ') 
from TableName.

Note: Point 1 query return if all columns have some value if anyone is null or empty then it will return null for all, means Name will return "NULL" value.

To avoid the point number 1, you can use point number 2 or point number 3 -

We can use IsNull or CONCAT keyword to get the same.

If anyone containing null value then ' ' (blank space) will add with next value.

Upvotes: 21

Views: 230542

Answers (16)

Teja Goud Kandula
Teja Goud Kandula

Reputation: 1574

There could be situations where there are multiple spaces in FirstName or MiddleName or LastName. This logic handles all of those situations.

with cte as (
select 'A' as FirstName, 'B' as MiddleName , 'C' as Lastname
union all 
select 'A' as FirstName, 'B' as MiddleName, null as Lastname
union all 
select 'A' as FirstName, null as MiddleName, 'C' as Lastname
union all 
select 'A' as FirstName, null as MiddleName, null as Lastname
union all 
select null as FirstName, 'B' as MiddleName, 'C' as Lastname
union all 
select null as FirstName, 'B' as MiddleName, null as Lastname
union all 
select null as FirstName, null as MiddleName, 'C' as Lastname
union all 
select null as FirstName, null as MiddleName, null as Lastname
union all 
select 'A' as FirstName, 'B' as MiddleName, 'C' as Lastname
union all 
select 'A' as FirstName, 'B' as MiddleName, ' ' as Lastname
union all 
select 'A' as FirstName, ' ' as MiddleName, 'C' as Lastname
union all 
select 'A' as FirstName, ' ' as MiddleName, ' ' as Lastname
union all 
select ' ' as FirstName, 'B' as MiddleName, 'C' as Lastname
union all 
select ' ' as FirstName, 'B' as MiddleName, ' ' as Lastname
union all 
select ' ' as FirstName, '  ' as MiddleName, 'C' as Lastname
union all 
select ' ' as FirstName, ' ' as MiddleName, ' ' as Lastname
)
select 
    FirstName, MiddleName, Lastname,
    TRIM(
        case
            when (FirstName is null or TRIM(FirstName) = '') and (MiddleName is null or TRIM(MiddleName) = '') and (Lastname is null or TRIM(Lastname) = '') 
                then null
            when (MiddleName is null or TRIM(MiddleName) = '')  
                then concat( FirstName,' ', Lastname)
            when (MiddleName is not null and TRIM(MiddleName) <> '')
                then concat( FirstName,' ', MiddleName, ' ', Lastname)
        end 
    )   as name
from cte

Upvotes: 0

Sameer
Sameer

Reputation: 381

Replace function removes two character white-spaces while concatenating First, Middle and Last Name.

SQL2016 and above compatible code

SELECT REPLACE(CONCAT(FirstName+' ',MiddleName+' ',LastName),'  ',' ') 
AS EmployeeName
FROM dbo.Employee

SQL SERVER 2017 Compatible code:

  SELECT REPLACE(CONCAT_WS(' ',FirstName,MiddleName,LastName),'  ',' ')AS 
  EmployeeName FROM dbo.Employee

Upvotes: 16

Sathish
Sathish

Reputation: 79

Replacing empty space in between middle name & last name and also removing the leading and trailing whitespaces.

SELECT TRIM(REPLACE(CONCAT(FirstName+' ',MiddleName+' ',LastName+' '),'  ',' ')) 
AS EmployeeName FROM dbo.Employee

Upvotes: 0

Nabiah Hassan
Nabiah Hassan

Reputation: 1

select concat(first_name,' ',last_name) as full_name from patients

patients is the table name which has the first name and last name column. here I want to combine the first and last names in the new column, which is the full name so I'm using CONCAT. Concat is an SQl server function that takes a variable number of string arguments and concatenates (JOIN) them into a single string. Quote marks for making a single space between first and last names.

Upvotes: 0

Bola Adel Nassif
Bola Adel Nassif

Reputation: 76

This Worked for me.

If you have SqlServer 2014 (or Lower).

Select
REPLACE(
    REPLACE(ISNULL(FirstName,''),' ','')
    +' '+
    REPLACE(ISNULL(MiddleName,''),' ','')
    +' '+
    REPLACE(ISNULL(LastName,''),' ','')
,'  ',' ') As [FullName]
From [Table];
GO

The benefits of using this code are:

  • it removes (NULL) and (Empty) names.
  • names contain more than one space (at the beginning) or (at the end) or (between the letters of the name) are removed.

Edit 1: (2023-02-13)
I found a better answer (more readable),
I will just write it here for future reference.

using --> Internal IF (IIF)

SELECT
    REPLACE(
        CONCAT(
            TRIM([FirstName])
            ,
            IIF([FirstName] + [MiddleName] IS NULL, '', ' ')
            ,
            TRIM([MiddleName])
            ,
            IIF([MiddleName] + [LastName] IS NULL, '', ' ')
            ,
            TRIM([LastName])
        )
    ,'  ',' ') As [FullName]

FROM <[Table]>;
GO

The benefits of using this answer are:

  • CONCAT() --> will replace all ISNULL() as it returns '' (Empty string) if the passed parameter is NULL, and also it implicitly converts passed parameters to string and merges them.
  • IIF(<condition>, true, false) --> i use it to check left & right before writing ' ' (space) between names.
  • Note: TRIM() was introduced in Sql Server 2016 (and later).

Upvotes: 1

Abdumalik Xoshimov
Abdumalik Xoshimov

Reputation: 49

if I understand it correctly u need to combine two values from diff tables!

select surname 
    from cd.members
union
select name
    from cd.facilities;

Upvotes: 0

xXx JOKER xXx
xXx JOKER xXx

Reputation: 39

SELECT CONCAT(first_name , ' ', last_name) as COMBINED_NAME
FROM TableName

Upvotes: 0

Osama Sultan
Osama Sultan

Reputation: 21

select concat(firstname ,' ',middlename, ' ',lastname ) as fullname from xyz

Upvotes: 0

Manish Kushwaha
Manish Kushwaha

Reputation: 1

select FirstName, MiddleName, LastName, Concat(FirstName,' ', isnull(MiddleName+' ',''), LastName) as fullname from Tablename

Upvotes: -1

alejandrob
alejandrob

Reputation: 681

A sure-fire way to list names with or without a Middle Name AND prevent convoluted function evaluations, is to deal with double spaces themselves and nothing else:

select REPLACE(FirstName + ' ' + MiddleName + ' ' + Lastname
               , '  ', ' ') as Name
from TableName

Upvotes: 0

Yihao Gao
Yihao Gao

Reputation: 553

This handles the case that...

  • any name field could be NULL or blank,
  • names could have double or more spaces at the middle of them (not like using REPLACE), and
  • compatible with even SQL Server 2008 (at least from what I've tested):
SELECT RTRIM(ISNULL(NULLIF(LTRIM(RTRIM(T.FirstName)), '') + ' ', '') +
             ISNULL(NULLIF(LTRIM(RTRIM(T.MiddleName)), '') + ' ', '') +
             ISNULL(NULLIF(LTRIM(RTRIM(T.LastName)), ''), '')) AS name
FROM TableName T
  • NULLIF(LTRIM(RTRIM(T.FirstName)), '') + ' ' evaluates to NULL if the name is blank because anything concatenate with NULL is NULL. Otherwise, it returns a trimmed name with a trailing space.
    • LTRIM and RTRIM removes the spacing at the start and the end of the value. If you are using SQL Server 2017 or later, you can also use TRIM instead.
    • NULLIF returns a null value if the two specified expressions are equal. In this case, we return a null value if the name is empty.
  • ISNULL replaces NULL with the specified replacement value. The first parameter is only NULL if the name is NULL or blank, then replace with an empty VARCHAR, which means there is no extra space introduced when concatenating with the rest of the name fields.
  • The outer RTRIM removes the last potential space if the last name is NULL or blank, but not first name or middle name.

Upvotes: 1

Nilesh Wagh
Nilesh Wagh

Reputation: 99

select CONCAT(FirstName , " ", MiddleName, " ", LastName  )as Name from 
  TableName

by using "" my issue is solved

Upvotes: 3

seantunwin
seantunwin

Reputation: 1768

  • Using a combination of RTRIM and LTRIM will strip any white-space on each end.
  • CONCAT to append the name segments together
  • COALESCE to replace NULL with an empty string

Formatted for readability

SELECT 
    RTRIM(LTRIM(
        CONCAT(
            COALESCE(FirstName + ' ', '')
            , COALESCE(MiddleName + ' ', '')
            , COALESCE(Lastname, '')
        )
    )) AS Name
FROM TableName

Upvotes: 27

t-clausen.dk
t-clausen.dk

Reputation: 44326

SELECT rtrim(concat(FirstName + ' ', MiddleName + ' ', LastName + ' '))

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37377

Well, I guess you looking for:

select Isnull(FirstName,'') +' '+ Isnull(MiddleName,'')+' '+ Isnull(Lastname,'') from TableName

That is, when any name is null it will be replace with empty string ''.

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Just Do A replaces for Double Space with Single Space on #3 and it should work as expected

SELECT 
    LTRIM(RTRIM(REPLACE(
    ISNULL(FirstName,' ') 
    +' '+ 
    ISNULL(MiddleName,' ')
    +' '+ 
    ISNULL(Lastname,' '),
    '  ',' ')))
FROM TableName

Upvotes: 4

Related Questions