COALESCE function in SQL Server with example

-It takes multiple parameters

-ANSI Standard

-Return 1st parameter if it is Not NULL.

Syntax: COALESCE( expression1, expression2, ... expression_n )

Example:

1) SELECT COALESCE(NULL, 'SQL Server', 'Rohit techvlog');

Result: SQL Server

SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4);

Result: 1

2) It accepts multiple parameter and returns first Not NULL argument

declare @first as int=NULL
declare @sec as int=1234
declare @third as int=12
select COALESCE(@first,@sec,@third) as ColParam

Result: 1234

3) COALESCE will not support NULL parameter as replacement argument

select COALESCE(NULL,NULL) as IsNUllParam

Error Output:

At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

4) COALESCE will not depend on the length of the datatype, it will return the whole string

declare @first as varchar(3)=NULL
declare @sec as varchar(10)='123456'
declare @third as varchar(4)='5689'
select COALESCE(@first,@sec) as IsNUllParam

Result: 123456