Using COALESEC Function in SQL SERVER

COLAESEC() Function

When developing queries in SQL Server, dealing with nulls can often be challenging. The COALESCE function was included in SQL 2005 to help deal with these issues. First let’s take a look at what COALESCE can offer. Generally, COALESCE function receives a sequence of values and a default value to used when all of items in the sequence of values are null. From here the function returns the first not-null in the sequence of values list.

Scenario

There is a requirement of showing a user’s full name and how much money they get paid per week. This scenario will be divided up into two segments, displaying the user’s full name and then computing and showing their weekly earnings. Please feel free to use the attached database to follow along.

Implementation

In this first example, suppose there is a table of users that have the columns FirstName, MiddleName and LastName. The table holds the following values:

In many applications, the requirement of welcoming the user is often needed. So, to put the user’s full name on the screen, a stored procedure using the COALESC function properly format all three fields into one field. 

So the query using COALESCE looks like this:

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

The results will look like this:

 

Thanks and happy coding!