NULLIF returns NULL if both of the strings are equal else returns first string.
SELECT NULLIF(‘MakeFlag’, ‘MakeFlag’)AS ‘Null if Equal’
Output : NULL
SELECT NULLIF(‘FinishedGoodsFlag’, ‘MakeFlag’)AS ‘Null if Equal’ — FinishedGoodsFlag
NULLIF can be implemented using CASE statement. It will compare two parameters and return NULL if both expressions are same.
SELECT ‘Null if Equal’ =
WHEN ‘MakeFlag’ = ‘FinishedGoodsFlag’ THEN NULL
Output : FinishedGoodsFlag
ISNULL is used to replace value of expression, if it comes to NULL.
SELECT ISNULL(NULL, expression)
DECLARE @var varchar(50)
SELECT ISNULL(@var, ‘No value has been assigned to variable.’)
Output : No value has been assigned to variable.
ISNULL and NULLIF are exactly opposite to each other.
ISNULL returns NULL if comparison becomes successful.
On the other hand, NULLIF returns not null if comparison becomes successful.
Pawan Kumar / Gauri D
“ISNULL returns NULL if comparison becomes successful.
On the other hand, NULLIF returns not null if comparison becomes successful.”
is it correct? if it is correct statement could you please explain me by taking an example…. thank u
Pawan Kumar Khowal said:
Yes you are correct ! Please find the examples below
–Example of ISNULL
–Here we are checking if the Input value is NULL, then we are placing it by Pawan
DECLARE @t AS VARCHAR(10) = NULL
SELECT ISNULL(@t, ‘Pawan’)
–Example of NULLIF, Here we are checking whether the input value is Pawan
–IF yes then we are returning NULL
DECLARE @t1 AS VARCHAR(10) = ‘Pawan’
SELECT NULLIF(@t1, ‘Pawan’)