Redshift DECODE Function Syntax and Examples

  • Post author:
  • Post last modified:February 23, 2021
  • Post category:Redshift
  • Reading time:4 mins read

Amazon Redshift supports the DECODE function and it is similar to DECODE function in other relational databases such as Oracle, SQL Server, MySQL, Netezza, etc. You can use the Redshift DECODE function to implement the simple CASE conditional statement or an if-then-else statement. This command is a shorthand form of the Redshift CASE Conditional function.

Redshift DECODE Function

A DECODE expression in Redshift replaces a specific value with either another specific value or a default value, depending on the result of an equality condition. The DECODE function operation is equivalent to the operation of a simple CASE expression or an IF-THEN-ELSE condition statement.

Redshift DECODE Function Syntax

Below is the syntax of the DECODE function. The syntax is very much similar to DECODE implemented in any other database.

DECODE ( expression, search, result [, search, result ]... [ ,default ] )

Where,

  • expression: is the source of the value that you want to compare, such as a column in a table.
  • search: is the target value that is compared against the source expression.
  • result: is the replacement value that query returns when the expression matches the search value.
  • default: is an optional default value that is used for cases when source and target comparison failed.

You can provide any number of target search expression in your DECODE statement.

Note that, if the source expression value and the target search value are both NULL, the DECODE result is the corresponding result value. The DECODE considers both NULL as a matching condition.

Redshift DECODE Function Examples

There are various usage of the DECODE statement in databases. For example, convert abbreviation to actual values.

Below are some of basic examples of using DECODE function.

SELECT Decode(cnt, 1, 'One', 
                   2, 'Two', 
                   3, 'Three', 
                   'None') AS counters 
FROM   (SELECT 1 AS cnt 
        UNION ALL 
        SELECT 2 AS cnt 
        UNION ALL 
        SELECT 3 
        UNION ALL 
        SELECT 4 AS cnt) a;

 counters
----------
 One
 Two
 Three
 None
(4 rows)

Related Articles

Hope this helps 🙂

Tags: ,