Sql Query to get the Full name from the table Employee Eliminating Null Value

Question

I have an EmpName Table.

Select * from EmpName

NameID FirstName MiddleName LastName
1       Sam         NULL      NULL
2       NULL        Todd      Tarzan
3       NULL        NULL      Sare
4       Ben         Parker    NULL
5       James       Nick      Nancy

Now I write the following query to get the fullname as

Select FirstName + ' ' + MiddleName + ' ' + LastName 
As FullName from EmpName

But I am getting the following result -

FullName
   NULL
   NULL
   NULL
   NULL
James Nick Nancy

But I want the following result -

FullName
   Sam
Todd Tarzan
   Sare
Ben Parker
James Nick Nancy

Is it - String concat with Null returns the Null ?

How can I get the FullName whose MiddleName or LastName has the value Null

@GordonLinoff Yes, but during tagging, from the stackoverflow suggestion, it prompts to tag MySql to reach maximum number of views. I just follow the stackoverflow suggestion. — royki, Jul 16 '15 at 12:12
@MartinSmith: Not an exact duplicate since concatenating is only part of assembling the full name — Andomar, Jul 16 '15 at 12:17

Gordon Linoff · Accepted Answer · 2015-07-17T02:56:13.060

3

When you concatenate strings, NULL takes precedence. So, use COALESCE(). This is a pain with separators, but the following should do what you want:

Select ltrim(coalesce(' ' + FirstName, '') +
             coalesce(' ' + MiddleName, '') +
             coalesce(' ' + LastName)
            ) as FullName
From EmpName;

edited Jul 17 '15 at 02:56

answered Jul 16 '15 at 12:10

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 2 · answered Jul 16 '15 at 12:11

2

when you try to concate string with NULL it always give null

Select isnull(FirstName,'') + ' ' + isnull(MiddleName,'') + ' ' + isnull(LastName,'') 
As FullName from EmpName

if you use sql server 2012 or later version

Select concat(FirstName,MiddleName,LastName) as FullName 
As FullName from EmpName

answered Jul 16 '15 at 12:11

Dhaval

2,341
1
13
16

+1 Didn't know about [concat](https://msdn.microsoft.com/en-us/library/hh231515.aspx) Still, it does not appear to add spaces between parts of the name. You could use `rtrim(concat(Firstname + ' ', Middlename + ' ', LastName + ' '))` – Andomar Jul 16 '15 at 12:20

score 1 · Answer 3 · answered Jul 16 '15 at 12:09

1

You need to use a MySQL String function such as CONCAT().

Example:

mysql> SELECT CONCAT('My', 'S', 'QL');

More information here: https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

answered Jul 16 '15 at 12:09

Mark

5,437
2
19
29

score 0 · Answer 4 · answered Jul 16 '15 at 12:10

0

Issue about null Try this query

Select isnull(FirstName,'') + ' ' +isnull(MiddleName,'') + ' ' + isnull(LastName ,'')
As FullName from EmpName

answered Jul 16 '15 at 12:10

Mukesh Kalgude

4,814
2
17
32

Stanislovas Kalašnikovas · Answer 5 · 2015-07-16T12:28:18.930

0

You can use COALESCE to avoid NULLS in following:

SELECT LTRIM(
       COALESCE(FirstName, '')  + ' ' + 
       COALESCE(MiddleName, '') + ' ' + 
       COALESCE(LastName, '') 
            ) AS FullName 
FROM EmpName

edited Jul 16 '15 at 12:28

answered Jul 16 '15 at 12:10

Stanislovas Kalašnikovas

9,203
4
32
59

This approach would name yourself as `Stanislovas DOUBLE SPACE Kalasnikovas` – Andomar Jul 16 '15 at 12:22

score 0 · Answer 6 · answered Jul 16 '15 at 12:10

NULLs propagate through a concatenation expression, hence your results coming through as NULL

To achieve what you're after you'd need to use ISNULL OR COALESCE:-

Select ISNULL(FirstName,'') + ' ' + ISNULL(MiddleName,'') + ' ' + ISNULL(LastName,'')
As FullName from EmpName

OR

Select COALESCE(FirstName,'') + ' ' + COALESCE(MiddleName,'') + ' ' + COALESCE(LastName,'')
    As FullName from EmpName

Andomar · Answer 7 · 2015-07-16T12:15:42.277

0

When you add null to a string (or anything else), the result is null:

select  'a' + null
-->
null

You can use isnull function to provide an alternative value for null columns. By adding + ' ' inside the isnull call, you only get the space if that part of the name is not empty:

select  rtrim(isnull(FirstName + ' ', '') +
            isnull(MiddleName + ' ', '') + 
            isnull(LastName + ' ', ''))

edited Jul 16 '15 at 12:15

answered Jul 16 '15 at 12:10

Andomar

232,371
49
380
404

score 0 · Answer 8 · answered Jul 16 '15 at 12:23

It is depend on database you are using,

-- SQL Server / Microsoft Access--

SELECT FirstName + ' ' + LastName As FullName FROM Employee

-- Oracle--

SELECT FirstName || ' ' || LastName As FullName FROM Employee

-- MySQL---

SELECT CONCAT(FirstName, ' ', LastName) As FullName FROM Employee

score 0 · Answer 9 · answered Jul 16 '15 at 13:16

Try this also:

DECLARE @FirstName VARCHAR(10) = '  John  '
DECLARE @MiddleName VARCHAR(10) = NULL-- '  David   '
DECLARE @LastName VARCHAR(10) = '    A    '
DECLARE @FullName VARCHAR(100)

SELECT @FullName = COALESCE(LTRIM(RTRIM(@FirstName)), '') + 
                   COALESCE(' ' + LTRIM(RTRIM(@MiddleName)), '') + 
                   COALESCE(' ' + LTRIM(RTRIM(@LastName)), '')

SELECT @FullName AS [Name], LEN(@FullName) AS [Length]

For your query

SELECT
    FullName = COALESCE(LTRIM(RTRIM(FirstName)), '') + 
               COALESCE(' ' + LTRIM(RTRIM(MiddleName)), '') + 
               COALESCE(' ' + LTRIM(RTRIM(LastName)), '')
FROM
    Employee

score 0 · Answer 10 · answered Oct 03 '17 at 09:35

0

select ISNULL(FIRSTNAME,'') + 
        (CASE WHEN ISNULL(FIRSTNAME,'') = '' THEN ISNULL(LEFT(MIDDLENAME,1),'') ELSE ' ' + ISNULL(LEFT(MIDDLENAME,1),'') END) + 
        (CASE WHEN ISNULL(FIRSTNAME+MIDDLENAME,'') = '' THEN LASTNAME WHEN Isnull(MiddleName,'') = '' THEN LASTNAME 
         ELSE ' ' + LASTNAME END) AS FullName 
FROM tblEmp

answered Oct 03 '17 at 09:35

Mayank Prajapati

68
4

You should provide an explanation : [How do I write a good answer ?](https://stackoverflow.com/help/how-to-answer) – Mickael Oct 03 '17 at 10:04

Sql Query to get the Full name from the table Employee Eliminating Null Value

10 Answers10