Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

Use cases for NULLIF

27 April,2018 by Tom Collins

Question: I understand the theory behind NULLIF – based on the MS documentation “Returns a null value if the two specified expressions are equal”.

But I’ve rarely had the opportunity to apply this function in real word scenarios . Can you expand on some real world scenarios for NULLIF ?

Answer: You are right – it’s not a widely used function , I think mainly because the more widely used expression CASE is used

Basically

NULLIF(value1,value2)

Is the equivalent to

CASE WHEN value1 = value2 THEN NULL ELSE value1 END

 

Using CASE gives you the advantage of utilising other expressions within the CASE structure.

I’ve seen also used to identify empty strings and convert the empty string into a NULL. So that would be : NULLIF(col1, '').   I prefer to not convert to NULL as that can lead to ambiguity , so I would translate into something else – such as a -1

As a general usage pattern – NULLIF is useful in cleansing data. You may need to prepare some data or integrate different data sets and find there are all sorts of empty strings, NULLs etc.

Developers have been known to avoid \workaround the classic “Division by zero” error and utilise the NULLIF.

 

--will raise an Divide by zero error encountered if the denominator = 0 
select value1 / value2
from (
	select 1 as value1, 0 as value2
	) t1

--will not raise an error if the numerator has a value and denoninator = 0
select value1 / nullif (value2, 0)
from (
	select 1 as value1, 0 as value2 
) t2


Read More on t-sql

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Use cases for NULLIF


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer