ISNULL( ) function replaces the Null value with placed value. The use of ISNULL ( ) function is very common in different situations such as changing the Null value to some value in Joins, in Select statement etc.
NULLIF ( ) function returns us Null if two arguments passed to functions are equal. NULLIF( ) function works like Case statement. If both arguments are not same then it will return us first argument value.
Let's create small example to see these functions in action
See the records of #Temp Table by using Select statement.
Now we want to replace Null in LName column with "Unknown" if any value is Null. We also want to create new output column in our Select statement with name "ColValueNotEqual". If FName and LName are different then we want to get FName value and If FName and LName are same then we want to get Null for this column. Let's use ISNULL ( ) and NULLIF ( ) functions to get required results.
NULLIF ( ) function returns us Null if two arguments passed to functions are equal. NULLIF( ) function works like Case statement. If both arguments are not same then it will return us first argument value.
Let's create small example to see these functions in action
SELECT *INTO #tmp
FROM (SELECT N'1' AS [Id], N'Aamir' AS [FName], N'Shahzad' AS [LName] UNION ALL SELECT N'2' AS [Id], N'Robert' AS [FName], N'Ladson' AS [LName] UNION ALL SELECT N'3' AS [Id], N'John' AS [FName], N'Rivers' AS [LName] UNION ALL SELECT N'4' AS [Id], N'Mike' AS [FName], NULL AS [LName] UNION ALL SELECT N'4' AS [Id], N'Tina' AS [FName], N'Tina' AS [LName]) t; SELECT [Id], [FName], [LName]FROM #tmp
See the records of #Temp Table by using Select statement.
Fig 1: #Temp table Records
Now we want to replace Null in LName column with "Unknown" if any value is Null. We also want to create new output column in our Select statement with name "ColValueNotEqual". If FName and LName are different then we want to get FName value and If FName and LName are same then we want to get Null for this column. Let's use ISNULL ( ) and NULLIF ( ) functions to get required results.
SELECT [Id], [FName], ISNULL([LName], 'Unknown') AS LName, NULLIF(FName, LName) AS ColValueNotEqual
FROM #tmp
Fig 2: ISNULL() and NULLIF() in TSQL
In Fig 2, We can see that LName Null value is replaced by "Unknown" as we have used ISNULL() function. ColValueNotEqual is returning us FName in case when FName and LName are not equal and returning us Null when FName and LName column's values are equal as we have used NULLIF() function.
No comments:
Post a Comment