Prashanth Jayaram

Using the SQL Coalesce function in SQL Server

September 20, 2018 by

String manipulation is a process to generate another form of existing data in a way the business uses or displayed as results in the reports.  Previous SQL string function articles, I have written, including SQL string functions for Data Munging and SQL Substring function overview discussed data preparation and data management tasks using built-in SQL Server string functions.

SQL server also has some built-in character functions that also allow us to manipulate and transform data.  At the same time, it is important to examine dataset, explore data values and encode or decode the values, as necessary, to generate meaningful data. It is important to know how to navigate through missing values in our datasets, understanding impact on calculations, queries, reports and data-set preparation and coming up with techniques to avoid letting Null values ruin our resultsets.

What is a NULL value?

Before we delve into how to navigate the potential minefield of datasets with missing values and avoid stepping on a Null, let’s first take a quick look at what a NULL is.

As defined by Wikipedia

Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of “missing information and inapplicable information”. Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. In SQL, NULL is a reserved word used to identify this marker. … This should not be confused with a value of 0. A null value indicates a lack of a value — a lack of a value is not the same thing as a value of zero in the same way that a lack of an answer is not the same thing as an answer of “no”.

Furthermore …

SQL null is a state, not a value. This usage is quite different from most programming languages, where null value of a reference means it is not pointing to any object.

SQL does provide some handy functionality for working with your character data in your SQL queries that we’ll describe in detail 

SQL Coalesce function

The SQL Server Coalesce and IsNull functions are used to handle NULL values. During the expression evaluation process the NULL values are replaced with the user-defined value.

The SQL Coalesce function evaluates the arguments in order and always returns first non-null value from the defined argument list.

Syntax

COALESCE ( expression [ 1…n ] )

Properties of the Coalesce SQL function

  1. Expressions must be of same data-type
  2. It can contain multiple expressions
  3. The SQL Coalesce function is a syntactic shortcut for the Case expression
  4. Always evaluates for an integer first, an integer followed by character expression yields integer as an output.

Examples:


SQL Coalesce in a string concatenation operation

In the following example, we’re going to concatenate some values. But, again, it’s just a review to let you know what happens when we have a NULL value. So, let’s go ahead and execute the T-SQL. And we can see that we encounter a NULL value while processing string concatenation operation. SQL server simply returns NULL whenever it encounters NULL value. The result is not a combination of the firstname, null, and last name.

Let us handle the NULL values using a function called COALESCE. It allows handling the behavior of the NULL value. So, in this case, use the coalesce SQL function to replace any middle name NULL values with a value ‘ ‘ (Char(13)-space). The SQL statement should still concatenate all three names, but no NULL values will show up in the output. We now see that the full name gets displayed with a space in the middle, for NULL values. In this way, it is possible to efficiently customize the column values.


SQL Coalesce function and pivoting

The following example returns the concatenated non-null values from the table ‘state’. In some cases, you may need to assign the concatenated static values to a variable. In this case, the values of the city column are parsed using the Coalesce SQL function and concatenated within a single quote to prepare a string of values. The output is then further manipulated to remove the last character to fetch a valid string of input value.

Output:

Scalar user-defined function and SQL Coalesce function

A user-defined function is created to return a string specific to the provided input and then the output is grouped using a grouping clause. In the following example, the scalar valued function returns the concatenated string values separated by ‘,’ for a specified ‘City’ input. The following example returns an output where the state column is grouped and its cities values are concatenated and separated by a delimiter ‘,’ (comma). You can also user STRING_AGG if you’re using SQL Server 2017. You can refer more information with the article Top SQL string functions in SQL Server 2017

Here is how we call the function name dbo.tfn_CoalesceConcat in the select statement.

The output is a concatenated stream of values separated by a delimiter ‘,’


Data validation using SQL Coalesce function

In the following example, we are going to find the emergency employee contacts. Usually, in any organization, the employee’s phone numbers are listed under work, home, cell phone columns.

Let us see how to find employees where no emergency contacts are listed or, in other words, let’s pull all the details of the employee with emergency contacts.

In the following example, the tb_EmergencyContact holds the contact numbers of all employees.

The SQL Coalesce function is used to select the columns homephone, workphone, and cellphone. In case of NULL values, the value ‘NA’ (not applicable), a literal string is returned.


SQL Coalesce and Computed columns

The following example uses COALESCE to compare the values of the hourlywage, salary, and commission columns and return only the non-null value found in the columns.

The following T-SQL is used to list the total salary paid to all the employees


Now, Let us see an example to create a computed column with SQL Coalesce function in SQL Server

In general, we may need to use the expression in the tables. In tables, it is required to compute the values that are often calculated using several existing columns and with few scalar values of the table. Also, these columns are dependent on one or more other columns. In this way, we can create a computed column using the Coalesce SQL function so that NULL values are efficiently handled.

Now, you can see that a simple SELECT statement displays the pre-calculated results.

SQL COALESCE and CASE expression

The SQL COALESCE function can be syntactically represented using the CASE expression. For example, as we know, the Coalesce function returns the first non-NULL values.

SELECT COALESCE (expression1, expression2, expression3) FROM TABLENAME;

The above Coalesce SQL statement can be rewritten using the CASE statement.

The query returns the same result as the one that uses the COALESCE function.

Wrap Up

In this article, we discussed some tip and tricks to demonstrate the use of the SQL Coalesce function to query effectively with the T-SQL. We also discussed various use-cases of the SQL Coalesce function. It is also possible to optimize output by creating a computed column.

I hope you enjoyed this article on the Coalesce function in SQL Server. Feel free ask any questions in the comments below.

See more

FREE SQL tools for SQL coding, refactoring, productivity, formatting, plan analysis, instance discovery, multi-db script propagation, database text and object search, object decryption, SQL CI/CD/DLM/DevOps, and SQL script comparison





Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
Functions

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

769 Views