The SQL Server ISNULL function validates whether an expression is NULL or not. If it is NULL, the SQL ISNULL function will replace them with a replacement value. Otherwise, it will return the original value.
For example, while 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 SQL Server ISNULL function returns the result by accepting two arguments:
- Check_Expression: Please specify the valid expression or column name 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 a few null values in the LastName column, and [AddressLine2] holds only null values. In this SQL Server ISNULL example, we are going to replace the Lastname column with ‘tutorialgateway’ and the [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.
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 also use SQL Server variables.
ISNULL([LastName], 'tutorialgateway') AS [LastName]
In the below statement, We used the function to replace the values in the [AdressLine2] column with the. ‘** 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]