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

SQL Server ISNULL function in detail with example

The ISNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression

The following shows the syntax of the ISNULL() function:

ISNULL(expression, replacement)

The ISNULL() function accepts two arguments:

expression In this parameter, we specify the expression in which we need to check NULL values.

replacement is the value to be returned if the expression is NULL. The replacement must be convertible to a value of the type of the expression.

The ISNULL() function returns the replacement if the expression evaluates to NULL. Before returning a value, it implicitly converts the type of replacement to the type of the expression if the types of the two arguments are different.

In case the expression is not NULL, the ISNULL() function returns the value of the expression.

Example:

1) Return the 2nd argument if the first string is NULL value

SELECT ISNULL(NULL, 'SQLServer') result;

Since first string is not NULL value it will return 1st argument

SELECT ISNULL('Rohit techvlog', 'SQLServer') result;

2) ISNULL will support NULL parameter as replacement argument

select ISNULL(NULL,NULL) as IsNUllParam

Output

NULL

3) ISNULL depends on length of first datatype

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