The SQL Server ISNULL function validates whether an expression is NULL or not. If it is NULL, then the ISNULL function will replace them with a replacement value. Otherwise, it will return the original value.
For example, while you are selecting the discount price of all products, what if your result returns a few NULL values? We can use ISNULL in SQL Server to get rid of these situations.
SQL ISNULL Function Syntax
The syntax of the ISNULL function is
SELECT ISNULL([Check_Expression], [Replace_Expression]) FROM [Source]
If you observe the above syntax, the ISNULL function returns the result by accepting two arguments:
- Check_Expression: Please specify the valid expression or column name on which you want to check for NULL values. This will check for the NULL values inside this column.
- Replacing_Expression: Please specify the valid expression that you want to replace the NULL. This method will replace those 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 return.
For this SQL Server ISNULL function example, We use the below-shown data.
If you observe the above screenshot, there are few null values in the LastName column, and [AddressLine2] holds only null values. In this ISNULL example, we are going to replace the Lastname column with ‘tutorialgateway’ and [AdressLine2] column with ‘** Same as AdressLine 1 **’ using this function
SELECT [CustomerKey] ,[FirstName] ,ISNULL([LastName], 'tutorialgateway') AS [LastName] ,[EmailAddress] ,[YearlyIncome] ,[EnglishOccupation] ,[AddressLine1] ,ISNULL([AddressLine2], '** Same as AdressLine 1 **') AS [AddressLine2] ,[Phone] FROM [TenCustomers]
In the below SQL Server ISNULL statement, We used the function to replace the NULL values in the [Lastname] column with ‘tutorialgateway’ string.
ISNULL([LastName], 'tutorialgateway') AS [LastName]
In the below statement, We used the function to replace the values in [AdressLine2] column with ‘** Same as AdressLine 1 **’ string. We also reassigned the old name [AddressLine2] to that result using the ALIAS Column.
ISNULL([AddressLine2], '** Same as AdressLine 1 **') AS [AddressLine2]