Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

TSQL IsNull Function

1. TSQL IsNull Function

In SQL Server, in some cases we need to replace a null value with more descriptive value. For example, while we select a record from a table, we can replace the null value from a column into a more meaningful value. The TSQL IsNull function can do the trick for us.

2. TSQL IsNull Example

Now, have a look at the below query:

Fig 1. Sql Column with Two Null Values
Fig 1. Sql Column with Two Null Values

We use Microsoft supplied Pubs sample database to fetch the records from Titles table. In the result, at record numbers 7 & 10, the royalty columns are showing null entries. We can change the query to get a meaningful value for this column value as shown below:

Fig 2. TSQL Replace Null With Value
Fig 2. TSQL Replace Null With Value


  1. Here we use the IsNull function to check the null column value. This function takes two parameters.
  2. For the first parameter, we supply the column name which must be checked with the null value. In our case, we supplied the Royalty column.
  3. Third parameter is the replacement value. IsNull function will check for Null value and when it finds the null, it will replace the null with this second parameter value.
  4. In our case, we have two null values for the Royalty column and the result here shows the values are rightly converted into a numeric 0 to denote no royalty for those books.

3. TSQL IsNull Function Restrictions

When using the IsNull function, the replacement value should match with the data type of the first column. Look at the example below:

Fig 3. IsNull Replacement Value Data Type
Fig 3. IsNull Replacement Value Data Type


  1. Data Type of the Royalty column is int and we pass this as first parameter to the IsNull function.
  2. Royalty Column and replacement values are not matching as the second argument is not an integer but a string.
  3. When we execute the query, SQL Server reports the error stating ‘No. RY’ is not integer type.

4. Code Reference

Categories: SQL Server

Tags: ,

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.