DECODE ​

Replace given values for an expression with specified values or a default value. For example, you can use DECODE to replace numeric keys, IDs, and codes with meaningful character strings. This kind of conditional expression is similar to a simple CASE expression.

Syntax ​

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

Parameters ​

expression

An expression that is the target of the search condition, such as a column name.

search

A value that may exist in the expression, such as a value in a column. This value is equivalent to a when value in a CASE expression. The search expression must evaluate to a single fixed value. You cannot specify an expression that evaluates to a range of values, such as a range of dates or ages. You must specify separate search and result pairs for each value that you want to replace. The data types of all instances of the search expression must be the same or compatible. The expression and search parameters must also be compatible.

result

A single value that may be replaced with the existing value when the search condition returns true. The result value is equivalent to a then value in a CASE expression. You must include at least one search and result pair in the DECODE expression. The data types of all instances of the result expression must be the same or compatible. The result and default parameters must also be compatible.

default

A single value that may be replaced with the existing value when the search condition fails (returns false or unknown). The default value is equivalent to the else value in a CASE expression. If no default value is specified and the search condition fails, the DECODE expression returns null.

Examples ​

The following simple examples demonstrate the logic of the DECODE syntax. In the first query, 1 = 1 so 3 is returned. In the second query, 1 != 2 so 4 (the default value) is returned.

premdb=# select decode(1,1,3,4);
 decode 
--------
     3
(1 row)

premdb=# select decode(1,2,3,4);
 decode 
--------
     4
(1 row)

In this example, several winning team names are replaced with nicknames. No default value is provided.

premdb=# select season_name, 
decode(winners, 
  'Manchester United', 'Red Devils',
  'Manchester City', 'Citizens', 
  'Leicester City', 'Foxes', 
  'Arsenal', 'Gunners',
  'Blackburn Rovers', 'Rovers', 
  'Chelsea', 'Pensioners') 
from season where winners is not null order by 1,2;
 season_name |   decode    
-------------+------------
 1992-1993   | Red Devils
 1993-1994   | Red Devils
 1994-1995   | Rovers
 1995-1996   | Red Devils
 1996-1997   | Red Devils
 1997-1998   | Gunners
 1998-1999   | Red Devils
 1999-2000   | Red Devils
 2000-2001   | Red Devils
 2001-2002   | Gunners
 2002-2003   | Red Devils
 2003-2004   | Gunners
 2004-2005   | Pensioners
 2005-2006   | Pensioners
 2006-2007   | Red Devils
 2007-2008   | Red Devils
 2008-2009   | Red Devils
 2009-2010   | Pensioners
 2010-2011   | Red Devils
 2011-2012   | Citizens
 2012-2013   | Red Devils
 2013-2014   | Citizens
 2014-2015   | Pensioners
 2015-2016   | Foxes
(24 rows)

The following query provides a default value Other for winning teams that are not from London or Manchester.

premdb=# select season_name, 
decode(winners,'Manchester United','Manchester',
'Manchester City','Manchester',
'Arsenal','London',
'Chelsea','London',
'Other') city from season where winners is not null order by 2,1;
 season_name |    city    
-------------+------------
 1997-1998   | London
 2001-2002   | London
 2003-2004   | London
 2004-2005   | London
 2005-2006   | London
 2009-2010   | London
 2014-2015   | London
 1992-1993   | Manchester
 1993-1994   | Manchester
 1995-1996   | Manchester
 1996-1997   | Manchester
 1998-1999   | Manchester
 1999-2000   | Manchester
 2000-2001   | Manchester
 2002-2003   | Manchester
 2006-2007   | Manchester
 2007-2008   | Manchester
 2008-2009   | Manchester
 2010-2011   | Manchester
 2011-2012   | Manchester
 2012-2013   | Manchester
 2013-2014   | Manchester
 1994-1995   | Other
 2015-2016   | Other
(24 rows)

Parent topic:Conditional Expressions