Wednesday, 5 July 2017

Isnull Function in SQL Server with examples

In this article I will explain “what is isnull function in sql server and when we will use Isnull function”. Here I will explain all about Isnull function with examples, try to practice all these examples. Then it will become easy to remember.

What is isnull function in sql server:

Isnull function is used to "replace NULL values with the specified replacement value".

Important points to remember about Isnull Function:


1. Isnull function contains only two parameters/arguments.

Syntax: ISNULL (check_null, Replace_value)

2. Isnull function is a sql server specific function

Now we will see the behavior or usage of Isnull with examples:

Execute the below query in sql server management studio

select isnull(null,10)

The output of the above sql query is 10. Here what isnull function did is "it replaced the null value with 10"

Now, execute the below query in sql server management studio
select isnull(5,10)

The output of the above sql query is 5, because there are no null values to replace with 10.

Now I explain Isnull function with one more example by taking tables, this will help you to understand more clearly.

We have one table called employee, with below values

id
name
designation
supid
1
mohan
Manger
NULL
2
raj kumar
SE
1
3
bipul kumar
Manager
NULL
4
mrinal kumar
SE
2
5
jitendra kumar
SE
2

In the above table you don’t want to get the null values in the supid column, you want to show all null values as 0. In this case we have to use Isnull function like below

SELECT
[id], [name], [designation],
isnull([supid],0) as [supid]
FROM [emp]

If you execute the above query in sql server management studio, the output is

id
name
designation
supid
1
mohan
Manger
0
2
raj kumar
SE
1
3
bipul kumar
Manager
0
4
mrinal kumar
SE
2
5
jitendra kumar
SE
2

I hope now you have clear idea on isnull function in sql server. If you still have any more doubts on Isnull function, please comment in this page.

More topics in SQL Server:

Difference between DELETE and TRUNCATE in SQL Server

What is the difference between Where clause and having clause in SQL Server

What is Set operators? Types of Set Operators? Union, Intersect, Except in SQL Server with example

How to copy entire table into another table in SQL Server

SQL Query to get Department wise Maximum Salary Employee Details from SQL server Table

You can also search about this topic in google as:
what is the use of isnull function in sql server with example, isnull sql server, how can we use isnull in sql server, isnull function, using isnull, isnull function examples 

Freshers Registration Link

share facebook

Enter Your Email below to get Daily Job Alert in Your Mailbox