Sunday, January 31, 2016

SQL - COALESCE() Function

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.
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.
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.


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;

UserId
Topic
Comments
Phone1
Phone2
Phone3
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 Pollutions.
NULL
NULL
8856321456


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

UserId
Topic
ContactNumber
1
Population
1245679870
2
Pollution
6758901243
3
Population
9321456235
4
Pollution
8856321456
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
UserComments
Population could be the biggest problem for the world.,India and China is the biggest country for the Population.,

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.

No comments:

Post a Comment