CODEWITHSUNDEEP
sqlsql-server
royki
royki

Reputation: 1693

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

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

Upvotes: 2

Views: 14035

Answers (10)

Mayank Prajapati
Mayank Prajapati

Reputation: 37

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270733

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;

Upvotes: 3

Jesuraja
Jesuraja

Reputation: 3844

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

Upvotes: 0

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You can use COALESCE to avoid NULLS in following:

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

Upvotes: 0

Girdhar Singh Rathore
Girdhar Singh Rathore

Reputation: 5615

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

Upvotes: 0

Andomar
Andomar

Reputation: 238246

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

Upvotes: 0

Dhaval
Dhaval

Reputation: 2379

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

Upvotes: 2

Mat Richardson
Mat Richardson

Reputation: 3606

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

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Issue about null Try this query

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

Upvotes: 0

Mark
Mark

Reputation: 5787

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

Upvotes: 1

Related Questions