Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use DECODE Function in Snowflake: Syntax and Examples

In Snowflake, the DECODE function compares an expression to multiple search values and returns the corresponding result for the first match. If no match is found, it returns an optional default value or NULL if no default is provided.
📐

Syntax

The DECODE function syntax in Snowflake is:

  • expression: The value to compare.
  • search1, result1, search2, result2, ...: Pairs of values to compare against and their corresponding results.
  • default (optional): The value to return if no matches are found.

It works like a simple if-else chain matching the expression to each search value.

sql
DECODE(expression, search1, result1, search2, result2, ..., default)
💻

Example

This example shows how DECODE returns different strings based on the input number:

sql
SELECT DECODE(2, 1, 'One', 2, 'Two', 3, 'Three', 'Other') AS result;
Output
result Two
⚠️

Common Pitfalls

Common mistakes include:

  • Not providing a default value, which returns NULL if no match is found.
  • Using unmatched data types between expression and search values.
  • Confusing DECODE with CASE which is more flexible.

Always ensure your search values and expression are compatible types.

sql
/* Wrong: No default, returns NULL if no match */
SELECT DECODE(5, 1, 'One', 2, 'Two');

/* Right: Default provided to handle unmatched cases */
SELECT DECODE(5, 1, 'One', 2, 'Two', 'Unknown');
Output
NULL Unknown
📊

Quick Reference

PartDescription
expressionValue to compare
searchValue to match against expression
resultValue returned if expression matches search
defaultValue returned if no matches found (optional)
✅

Key Takeaways

DECODE compares an expression to multiple values and returns the first matching result.
Always provide a default value to avoid unexpected NULL results.
Ensure expression and search values have compatible data types.
DECODE is simpler but less flexible than CASE statements.
Use DECODE for quick equality checks in Snowflake SQL.