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
NULLif no match is found. - Using unmatched data types between expression and search values.
- Confusing
DECODEwithCASEwhich 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
| Part | Description |
|---|---|
| expression | Value to compare |
| search | Value to match against expression |
| result | Value returned if expression matches search |
| default | Value 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.
