Sunday, August 30, 2020

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.


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


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


No comments:

Post a Comment