The SQL ISNULL function validates whether an expression is NULL or not. If it is NULL then Sql Server ISNULL function will replace the null value with replacement value otherwise, it will return original value.
For example, while you are selecting the discount price of all products and, what if your result returns few NULL values. To get rid of these situations, we can use ISNULL in SQL Server.
SQL ISNULL Function Syntax
The basic syntax of the ISNULL function in SQL Server is as shown below:
SELECT ISNULL ([Check_Expression], [Replace_Expression]) FROM [Source]
If you observe the above syntax, SQL Server ISNULL function accepts two arguments:
- Check_Expression: Please specify the valid expression or column name on which you want to check for NULL values. ISNULL function will check for the NULL values inside this column.
- Replacing_Expression: Please specify the valid expression that you want to replace the NULL. ISNULL function will replace the NULL values inside the Check_Expression with this Replacing_Expression.
TIP: If the Check_Expression is NULL then Replacing_Expression will be returned otherwise, Check_Expression will be returned.
In this article we will show you, How to write ISNULL function with example. For this, We are going to use the below shown data
SQL ISNULL function
If you observe the above screenshot, there are few null values in LastName column and [AddressLine2] hold only null values. In this ISNULL function example we are going to replace the Lastname column with ‘tutorialgateway’ and [AdressLine2] column with ‘** Same as AdressLine 1 **’ using ISNULL function
SELECT [CustomerKey] ,[FirstName] ,ISNULL([LastName], 'tutorialgateway') AS [LastName] ,[EmailAddress] ,[YearlyIncome] ,[EnglishOccupation] ,[AddressLine1] ,ISNULL([AddressLine2], '** Same as AdressLine 1 **') AS [AddressLine2] ,[Phone] FROM [SQL Tutorial].[dbo].[TenCustomers]
In below statement, We used Sql Server ISNULL function to replace the NULL values in [Lastname] column with ‘tutorialgateway’ string. We also reassigned the old name ‘LastName’ to that result using ALIAS Column.
TIP: Instead of directly writing the string value as second argument, you can use variables as well.
ISNULL([LastName], 'tutorialgateway') AS [LastName]
In below statement, We used ISNULL function to replace the NULL values in [AdressLine2] column with ‘** Same as AdressLine 1 **’ string. We also reassigned the old name [AddressLine2] to that result using ALIAS Column.
ISNULL([AddressLine2], '** Same as AdressLine 1 **') AS [AddressLine2]