SQL - COALESCE() - This function plays a very
important role in SQL because it will take any number of parameters, and return
the first value encountered that isn't NULL. All the passed parameters
into the function must have the same data type. If
all parameters are NULL, COALESCE returns NULL. At least one of the null values
must be a typed NULL.
COALESCE is ANSI standard function which is internally translated to a CASE expression and there is an advantage that we aware what to expect when using it. COALESCE is harder to spell, but at least it doesn't lead to incorrect.
Note: COALESCE requires at
least minimum of two expressions. If all the passed
parameters are not the same data-type then they get implicitly cast
to an appropriate data-type using data-type order of precedence.
COALESCE is ANSI standard function which is internally translated to a CASE expression and there is an advantage that we aware what to expect when using it. COALESCE is harder to spell, but at least it doesn't lead to incorrect.
SQL Syntax
COALESCE (
expression [ ,...n ] )
Arguments
Expression: Is an
expression of any type.
Return Types: Returns the data type of expression with the highest data type
precedence.
|
To understand the powerful features of COALESCE function;
we are taking an example to explain it. In this example, we are going to create
a temporary table to store the user comments on the various topics and their
contact numbers.
USE tempdb
GO
---- CREATE TEMP TABLE FOR COMMENTS
CREATE TABLE
#UserComments
(
UserId Int,
Topic varchar(200),
Comments varchar(500),
Phone1 Varchar(12),
Phone2 Varchar(12),
Phone3 Varchar(12)
)
---- insert data into comments table
INSERT INTO
#UserComments(UserId,Topic,Comments,Phone1,Phone2,Phone3)
VALUES(1,'Population','Population could be the
biggest problem for the world',1245679870,NULL,9852631450),
(2,'Pollution','Now a day, people are suffering lots of disease due to
Pollution',NULL,6758901243,NULL),
(3,'Population','India and China is the biggest country for the Population',9321456235,NULL,NULL),
(4,'Pollution','There are many type of the Pollution',NULL,NULL,8856321456)
----- pull the records from the comments table
SELECT UserId,Topic,Comments,
Phone1,Phone2,Phone3
FROM #UserComments;
---- drop temp table
DROP TABLE
#UserComments;
|
Return first Non-able Value
On the basis of the User
Comments table, we can get the any available contact number against any user Id
because COALESCE function can take n-numbers of the
arguments and will return the first not null able value from them as given
below:
---- Pull the contact number for the user
select
UserId,
Topic,
ContactNumber=Coalesce(Phone1,Phone2, Phone3)
from
#UserComments
|
Before coalesce function is run, it checks what
data types it is comparing. i.e. INT, INT, INT and DATETIME. It decides that
for this function they should all be processed as DATETIME and implicitly
converts them before it calls the function.
Using Coalesce to comma separated
values
In the above
table, we can use the coalesce function to get the comma separated values as
given below:
---- declare local variable
DECLARE @UserComments VARCHAR(1000)
----- get the vales into variable
SELECT @UserComments = COALESCE(@UserComments,'') + Comments + ','
FROM #UserComments
WHERE (Topic = 'Population')
----- pull value from variable
SELECT @UserComments AS UserComments
|
Conclusion
Coalesce allows multiple items to be compared in one statement. COALESCE
() most often appears within a very specific content, such as in a query or
view or stored procedure. COALESCE is a powerful tool if you are returning
numerous values to a user and want to substitute occurrences of NULL with
values from a different column or with an expression.
