Stay organized with collections
Save and categorize content based on your preferences.
Use NULLIF to selectively replace a specific value in your data with NULL. This can be helpful in situations where a particular value represents missing or invalid data, and you want to explicitly mark it as NULL for further analysis or processing.
Sample usage
Replace -1 in the Discount field with NULL.
NULLIF(Discount, -1)
Syntax
NULLIF( input_expression, expression_to_match )
Parameters
input_expression
The expression to evaluate. You can use any valid expression as the input_expression.
expression_to_match
NULLIF compares expression_to_match to input_expression. If the two are equal, NULLIF returns null, otherwise it returns the input_expression. You can use any valid expression as the expression_to_match.
Example
Suppose you want to calculate the average discount given to your customers. Your application represents "no discount" as -1. The formula AVG(Discount) will count -1 and return an incorrect result. To avoid this, you can convert those -1s to NULLS. To do this, create a new field called, for example, Discount With Nulls :
NULLIF(Discount, -1)
This formula can be read, "If the Discount field is -1, return null, otherwise return Discount."
You can then calculate the average discount ignoring orders with no discount:
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-07-22 UTC."],[],[],null,["# NULLIF\n\nUse `NULLIF` to selectively replace a specific value in your data with NULL. This can be helpful in situations where a particular value represents missing or invalid data, and you want to explicitly mark it as NULL for further analysis or processing.\n\nSample usage\n------------\n\nReplace -1 in the **Discount** field with NULL.\n\n`NULLIF(Discount, -1)`\n\nSyntax\n------\n\n`NULLIF( `\u003cvar translate=\"no\"\u003einput_expression\u003c/var\u003e`, `\u003cvar translate=\"no\"\u003eexpression_to_match\u003c/var\u003e` )`\n\n### Parameters\n\n#### input_expression\n\nThe expression to evaluate. You can use any valid expression as the \u003cvar translate=\"no\"\u003einput_expression\u003c/var\u003e.\n\n#### expression_to_match\n\n`NULLIF` compares \u003cvar translate=\"no\"\u003eexpression_to_match\u003c/var\u003e to \u003cvar translate=\"no\"\u003einput_expression\u003c/var\u003e. If the two are equal, `NULLIF` returns null, otherwise it returns the \u003cvar translate=\"no\"\u003einput_expression\u003c/var\u003e. You can use any valid expression as the \u003cvar translate=\"no\"\u003eexpression_to_match\u003c/var\u003e.\n\nExample\n-------\n\nSuppose you want to calculate the average discount given to your customers. Your application represents \"no discount\" as -1. The formula `AVG(Discount)` will count -1 and return an incorrect result. To avoid this, you can convert those -1s to NULLS. To do this, create a new field called, for example, **Discount With Nulls** :\n\n`NULLIF(`**`Discount`** `, -1)`\n\nThis formula can be read, \"If the **Discount** field is -1, return null, otherwise return **Discount**.\"\n\nYou can then calculate the average discount ignoring orders with no discount:\n\n`AVG(`**`Discount With Nulls`** `)`"]]
