How to Use COALESCE Function in SQL
In this article I am going to explain how to use COALESCE function in SQL.
Introduction
In SQL Server there are two type of function which are used to manipulate data in database.
- System define function
- User define function
SQL server provide a list of system define function which are used to manipulate data. These system define function are predefine. We can't change functionality of these system define function. There are many situation where we need a function which performer some specific task according to our need. There function are known as user define function.
COALESCE Function
COALESCE Function is used to find the not null value from a list of value. It returns the first expression in a list of expression that is not null. It returns null value if all the expression have null value. ISNULL also return not null value but COALESCE Function is more flexible in use than ISNULL function.
Statement show data from mcninvoices table
Statement that uses COALESCE Function
SELECT TOP 1 invoiceid,
COALESCE(paymenttotal,creadittotal,vendorid)
AS notnull_value FROM dbo.mcninvoices
ORDER BY invoiceid DESC
|
Output