This article explores the string manipulation using SQL Coalesce function in SQL Server.
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 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 SQL Coalesce function
- Expressions must be of same data-type
- It can contain multiple expressions
- The SQL Coalesce function is a syntactic shortcut for the Case expression
- Always evaluates for an integer first, an integer followed by character expression yields integer as an output.
Examples:
|
1 2 3 4 5 6 |
SELECT COALESCE (NULL,'A','B') SELECT COALESCE (NULL,100,20,30,40) SELECT COALESCE (NULL,NULL,20,NULL,NULL) SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Prashanth') SELECT COALESCE (NULL,NULL,NULL,NULL,1,'Prashanth') SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Prashanth',1) |
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.
|
1 |
SELECT firstName +' '+MiddleName+' '+ LastName FullName FROM Person.Person |
Let us handle the NULL values using a function called SQL 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.
|
1 |
SELECT firstName +' '+COALESCE(MiddleName,'') +' '+ LastName FROM Person.Person |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP TABLE IF EXISTS STATE; CREATE TABLE STATE ( CITY VARCHAR(50), STATE VARCHAR(500)) INSERT INTO STATE VALUES('Appleton','WI'),('Milwaukee','WI'),('Madison','WI'),('Miami','Florida'),('Jacksonville','Florida') DECLARE @col nvarchar(MAX); SELECT @col = COALESCE(@col,'') +''''+CITY +''''+ ',' FROM dbo.STATE WHERE state = 'WI'; SELECT substring(@col,1,len(@col)-1) |
Output:
|
1 |
SELECT '('+substring(@col,1,len(@col)-1)+')' |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE FUNCTION dbo.tfn_CoalesceConcat ( @state varchar(100) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @str NVARCHAR(MAX); SELECT @str = COALESCE(@str + ', ', '') + CITY FROM dbo.STATE WHERE state = @state ORDER BY state; RETURN (@str); END GO |
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 ‘,’
|
1 2 3 4 |
SELECT state, city = dbo.tfn_CoalesceConcat(state) FROM dbo.state GROUP BY state ORDER BY state; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DROP TABLE IF EXISTS tb_EmergencyContact; CREATE TABLE tb_EmergencyContact ( empid int, firstname VARCHAR(100) NOT NULL, lastname VARCHAR(100) NOT NULL, relationship VARCHAR(100), homephone VARCHAR(25), workphone VARCHAR(25), cellphone VARCHAR(25) ); INSERT INTO tb_EmergencyContact ( empid, firstname, lastname, relationship, homephone, workphone, cellphone ) VALUES ( 1, 'Ambika', 'Prashanth', 'Wife', NULL, '920.176.1456', '928.132.2967' ),( 2, 'Prashanth', 'Jayaram', 'spouse', NULL, NULL, '982.132.2867' ), ( 3, 'Pravitha', 'Prashanth', 'Daughter', NULL, NULL, NULL ) |
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.
|
1 2 3 4 5 6 |
SELECT firstname+''+lastname fullname, relationship, COALESCE(homephone, workphone, cellphone, 'NA') phone FROM dbo.tb_EmergencyContact |
SQL Coalesce and Computed columns
The following example uses SQL COALESCE to compare the values of the hourlywage, salary, and commission columns and return only the non-null value found in the columns.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE EMP (EMPNO INT NOT NULL, ENAME VARCHAR(20), JOB VARCHAR(10), MGR INT, JOINDATE DATETIME, HOURLYWAGE DECIMAL(7,2), SALARY DECIMAL(7, 2), COMMISSION DECIMAL(7, 2), NUMSALES DECIMAL(7,2), DNO INT) INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '02-MAR-1970',NULL, 8000, NULL, 20,2), (7499, 'ALLEN', 'SALESMAN', 7698, '20-MAR-1971',NULL, 1600, 3000,4, 3), (7521, 'WARD', 'SALESMAN', 7698, '07-FEB-1983', 40,1250, 5000,10, 3); |
The following T-SQL is used to list the total salary paid to all the employees
|
1 2 3 4 5 |
SELECT EMPNO,ENAME,CAST(COALESCE(HOURLYWAGE * 40 * 52, salary, Salary+(COMMISSION * NUMSALES)) AS decimal(10,2)) AS TotalSalary FROM dbo.EMP ORDER BY TotalSalary; |
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.
|
1 2 3 4 5 6 7 |
ALTER TABLE dbo.EMP ADD Total_Salary AS CAST(COALESCE(HOURLYWAGE * 40 * 52, salary, Salary+(COMMISSION * NUMSALES)) AS decimal(10,2)) select * from EMP |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT firstname+''+lastname fullname, relationship, CASE WHEN homephone is NOT NULL Then homephone WHEN cellphone is NOT NULL Then cellphone WHEN workphone is NOT NULL Then workphone ELSE 'NA' END EmergencyContactNumber FROM dbo.tb_EmergencyContact |
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.
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
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021