Archive

What is the Equivalent of Oracle's DECODE Function in SQL Server?

Oracle's DECODE function can use multiple arguments:

DECODE ( exp1, val1, ret1 [, val2, ret2] [, default]

exp1 is an expression. val1 is a matching expression to compare with exp1. If val1 is equivalent to exp1, then ret1 is returned; otherwise, additional matching expressions (val2, val3, val4, and so on) is returned. If no match is found and the default expression default is included, then default is returned.

The equivalent of Oracle's DECODE function in SQL Server is the CASE function.

CASE input_expression
    WHEN when_expression_1 THEN result_expression_1
  [ WHEN when_expression_2 THEN result_expression_2 
    WHEN when_expression_n THEN result_expression_n ]
  [ ELSE else_result_expression ]
END

This is the simple format of the CASE function, which compares an expression (input_expression) to a set of simple expressions (when_expression_n) to determine the result (result_expression_n).

input_expression is the expression evaluated when using the simple CASE format. WHEN when_expression is a simple expression to which input_expression is compared. THEN result_expression is the expression returned when input_expression equals when_expression evaluates to TRUE. ELSE else_result_expression is the expression returned if no comparison evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, the CASE function returns a NULL value.