How to Use COALESCE Function in SQL

In this article I am going to explain how to use COALESCE function in SQL.
  • 3143

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

Clipboard02.jpg

Statement that uses COALESCE Function

SELECT TOP 1 invoiceid,
COALESCE(paymenttotal,creadittotal,vendorid)
AS notnull_value FROM dbo.mcninvoices

ORDER BY invoiceid DESC


Output

Clipboard04.jpg


Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.