Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

Coalesce Function SQL Server Example



Introduction

String manipulation is the process for transforming existing data into another form that can be used by businesses to generate reports. SQL Server  has various built-in functions for manipulating and transforming data. On the other hand, it is important examining datasets, exploring data values, encode or decode values as required to generate more meaningful data.

It is also important to know how to navigate through missing values in datasets and understand the impact on queries, calculations, data-set generation, reports and come up with innovative techniques to avoid letting NULL values ruin our result sets.

Before we dive deep into the topic, let us first understand the NULL Values and their impact on datasets. It is necessary to have a clear idea of Null Values before you start learning the Coalesce SQL Server function .

Read: What is the Difference Between Control Flow & Data Flow in SSIS?

Null Values

Null is a special marker used in Structured Query Language to check whether a particular value exists or not in a database. This concept was introduced by the relational database model creator E.F. Codd. SQL Null values serve to fulfill the requirement that all true relational database management systems support a representation of missing and inapplicable values.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

Codd used the omega symbol to represent NULL in database theory. In SQL, NULL is defined as the reserved keyword to identify this marker. Null should not be misunderstood with a zero (0) value. Null indicates the lack of value, and it could not equal to zero. A lack of answer does not mean that you should write “no” there. Further, SQL NULL is not a value but a state. Its usage is quite different from other programming languages where the NULL value of a reference means it is not pointing to any object. SQL offers some handy functionality to work with the character data in SQL queries that we will be discussing in detail below.

Read: SQL Server Developer & Database Administrator Salary Structure

SQL Coalesce Function in SQL Server

The Coalesce function in the SQL server is used to define the Null values. During the expression evaluation process, Null values are generally replaced with user-defined values. The function evaluates arguments in a specific order and always returns non-null values first from the given arguments list. The basic syntax of the Coalesce function in the SQL server is given below. COALESCE (expression [1…n]) Here are some properties of Coalesce SQL function:

  • Expressions should be of the same data types.
  • It should contain multiple expressions together.
  • It is a syntactic shortcut for the CASE expression.
  • It evaluates for integers first, an integer is followed by the character expression yields integer as the final output.
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 Function in SQL Server

SQL Coalesce Function for String concatenation

Consider an example, you want to concatenate two strings together but what happens if the string has Null values. Let us move ahead and execute the T-SQL. As you parse through the string, you realized that there is a Null value in the string while processing string concatenation operation. SQL simply returns Null when it encounters the Null values. The sample syntax for string concatenation is given below.

Read: The Types Of Database Query And How To Use Them


SELECT firstName +' '+MiddleName+' '+ LastName FullName FROM Person.Person

SQL Coalesce Function for String concatenation So, how to handle these Null values. The best idea is using coalesce function allows handling behavior of Null values gracefully. In this example, Null values for the middle name will be replaced by space once the query is executed. The SQL query still concatenates three fields, first name, middle name, and the last name but no Null values are shown in the output. The full name will display with a space between first name and the last name.  Here is how to customize the same query using coalesce function in SQL Server.


SELECT firstName +' '+COALESCE(MiddleName,'') +' '+ LastName  FROM Person.Person

The final output will look like this: SQL Coalesce Function for String concatenation

SQL Coalesce Function and Pivoting

A user-defined function is used to return a string specific to the given input and output is grouped together using the group clause. For this example, the scalar-valued functions return the concatenated string values separated by commas for specified inputs. In this example, state values are grouped, city values are concatenated, and separated by a delimiter for the output. You may use String-AGG function too if using SQL Server 2017.


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

The output for this query will look like this:


SELECT state, city = dbo.tfn_CoalesceConcat(state)
  FROM dbo.state
  GROUP BY state
  ORDER BY state;

SQL Coalesce Function and Scalar user-defined functions

SQL Coalesce Function and Scalar user-defined functions

A user-defined function is used to return a string specific to the given input and output is grouped together using the group clause. For this example, the scalar-valued functions return the concatenated string values separated by commas for specified inputs. In this example, state values are grouped, city values are concatenated, and separated by a delimiter for the output. You may use String-AGG function too if using SQL Server 2017.

Read: MSBI Tutorial Guide for Beginners in 2020


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

The output for this query will look like this:


SELECT state, city = dbo.tfn_CoalesceConcat(state)
  FROM dbo.state
  GROUP BY state
  ORDER BY state;

SQL Coalesce Function and Scalar user-defined functions

How to validate data using SQL Coalesce Function?

In this section, we will try to find out the emergency employee contacts. For most organizations, the phone number of employees is listed under work, home, mobile number columns. So, how to find employees whose emergency contacts are not added in the list. Or you have to find out the list of employees with emergency numbers. Here is the sample program for data validation using Coalesce function in the SQL Server.


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 )

Here, the coalesce function is used to return columns with phone numbers, home, work, etc. and Null values are returned if emergency contacts are not added for a particular employee.


SELECT
  firstname+''+lastname fullname,
   relationship,
  COALESCE(homephone, workphone, cellphone, 'NA') phone
FROM
  dbo.tb_EmergencyContact

How to validate data using SQL Coalesce Function?

Coalesce SQL Function and column computation

The coalesce function can be used to compare values of hourly wages, salary, commission, etc. and function return the Null values for the columns if found any.


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);

Here is the list of total salary paid to all employees:


SELECT EMPNO,ENAME,CAST(COALESCE(HOURLYWAGE * 40 * 52,   
   salary,   
   Salary+(COMMISSION * NUMSALES)) AS decimal(10,2)) AS TotalSalary   
FROM dbo.EMP  
ORDER BY TotalSalary;

SQL Coalesce Function and column computation Moving ahead, let us see how to create a computed column with SQL Coalesce function in SQL Server. Generally, you should use the expressions in tables. In excel, it is required to compute values using several existing columns and a few scalar values within a table. Mostly these values are dependent on one or more columns. In this way, we can create a computed column using Coalesce function where Null Values can be managed even more efficiently.


ALTER TABLE dbo.EMP
ADD Total_Salary AS
      CAST(COALESCE(HOURLYWAGE * 40 * 52,   
   salary,   
   Salary+(COMMISSION * NUMSALES)) AS decimal(10,2))
 
select * from EMP

SQL Coalesce Function and column computation

Coalesce SQL Server function and CASE Expression

To represent SQL, coalesce function syntactically, we can use the CASE expression. The Case expression in SQL first returns the Non-Null values then it managed the Null values for the program effectively. The above Coalesce statement in SQL using Case expression can be rewritten as: This query will also return the same output as earlier:

Read: SAS Tutorial Guide for Beginners

How to compare Coalesce and Case Expression in SQL?

The coalesce expression is a syntactic shortcut of Case expression in SQL. The basic syntax of coalesce function in SQL server is written as:


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 same syntax using CASE expression can be rewritten as: SQL Coalesce function and CASE Expression In such cases, input values are evaluated multiple times. If there is one value expression that contains a subquery and considered non-deterministic, then the subquery is evaluated twice. In either case, the output value is calculated by working on first and upcoming evaluations. 

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

How to compare Coalesce and ISNULL functions?

Both functions are designed with the same objective, but they behave differently in certain situations as given below.

  • ISNULL is a function evaluated only once while the Coalesce SQL function is evaluated multiple times together.
  • ISNULL function uses the data type of the first parameter while Coalesce SQL function uses rules of CASE expression and returns the data type with high precedence.
  • Both functions have different nullability values even for the same expression.
  • Data validation rules for both functions are different. For example, the Null value for ISNULL function can be converted to int while Coalesce SQL function always uses a data type for the same purpose.
  • ISNULL function takes only two parameters while Coalesce SQL takes a multiple number of variables together.

Read: SQL Server Views - Everything You Should Know

Final Words

This blog explains tips and tricks on using SQL coalesce function or coalesce SQL Oracle effectively with the T-SQL. Coalesce function usually appears in a very specific content like query, view, stored procedure, etc. The usage of Coalesce function can be generalized by placing it within a function. You can also optimize its performance and make the results constantly available by placing it in a computed column.

We tried to include maximum information in the blog for your reference to use Coalesce function in different contexts. To know more about  Coalesce function and similar SQL concepts, you may join the SQL certification program and start your database learning now.

Read: How to Get Your Career as an SSIS Developer Rolling?

SQL Tutorial Overview


    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

14 days 12 Jun 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

6 days 04 Jun 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

10 days 08 Jun 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

4 days 02 Jun 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

1 day 30 May 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

3 days 01 Jun 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 03 Jun 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

10 days 08 Jun 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews