The SQL ISNULL function validates whether an expression is NULL or not. If it is NULL, then the SQL 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 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 syntax of the ISNULL function is
SELECT ISNULL ([Check_Expression], [Replace_Expression]) FROM [Source]
If you observe the above syntax, SQL Server 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 ISNULL function example, We use the below-shown data
ISNULL function Example
If you observe the above screenshot, there are few null values in the LastName column, and [AddressLine2] holds only null values. In this SQL 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 statement, We used the function to replace the NULL values in the [Lastname] column with ‘tutorialgateway’ string. We also reassigned the old name ‘LastName’ to that result using the ALIAS Column. Instead of directly writing the string value as the second argument, you can use SQL Server variables as well.
ISNULL([LastName], 'tutorialgateway') AS [LastName]
In the below SQL ISNULL 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]