Replace null with space (isnull and Coalesce)

ISNULL AND COALESCE

We have two functions which can be used to replace the null values with some other values. ISNULL and COALESCE.

ISNULL required two parameters: The value to check and the replacement for null values.

COALESCE works a little different way, this take any number of parameters and return the first non –null value.

In T-SQL generally preference given to COALESCE over ISNULL because COALESCE will take any number of parameters and return the first non-NULL value. Developers often

Prefer COALESCE over ISNULL because COALESCE meets ANSI standards, while ISNULL does not. Also, COALESCE is more handy.

Syntax for the two functions:

ISNULL(<value>,<replacement>)

COALESCE(<value1>,<value2>,...,<valueN>)

use TSQL2012

IF(OBJECT_ID( 'tempdb..#TableColaeseExample' ) IS NOT NULL)

        DROP TABLE #TableColaeseExample;

/*

       Creating Sample Table

*/

Create Table #TableColaeseExample

(

empId bigint identity(1,1),

FirstName varchar(100),

MiddleName varchar(100),

LastName varchar(100),

Region varchar(20)

)

/*

       Inserting sample Data

*/

Insert Into #TableColaeseExample values ('Arun',null,'Kumar','Delhi');

Insert Into #TableColaeseExample values ('Ranjan',null,'Sharma','Noida');

Insert Into #TableColaeseExample values ('Laxmi',null,'Azad','Kanpur');

Insert Into #TableColaeseExample values ('Rajesh','Kumar','Prajapati','Ghaziabad');

Insert Into #TableColaeseExample values ('Ravi','Ranjan','Kumar','Rohtak');

Insert Into #TableColaeseExample values ('Somen',null,null,'Noida');

Insert Into #TableColaeseExample values ('sikha',null,null,'Delhi');

--1

SELECT Region, FirstName + ' ' + ISNULL(MiddleName,'') +

' ' + LastName AS "Full Name"

FROM #TableColaeseExample;

--2

SELECT Region, FirstName + ISNULL(' ' + MiddleName,'') +

' ' + LastName AS "Full Name"

FROM #TableColaeseExample;

--3

SELECT Region, FirstName + COALESCE(' ' + MiddleName,'') +

' ' + LastName AS "Full Name"

FROM #TableColaeseExample;

Query 1 uses the ISNULL function to replace any missing MiddleName values with an empty string in order to build Full Name. Look in the results that whenever MiddleName is missing, you end up with two spaces between FirstName and LastName.

Line 3 in the results of query 1 contains two spaces  because a space is added both before and after the ISNULL function.

To rectify problem, move the space inside the ISNULL function instead of before it: ISNULL(' ' + MiddleName,''). Concatenating a space with NULL returns NULL. When the MiddleName value is NULL, the space is eliminated, and no extra spaces show up in your results. Instead of ISNULL, query 3 contains the COALESCE function. If MiddleName is NULL, the next non-NULL value, the empty string, is returned.