Usage of decode in sql

The DECODE function is used to evaluate different values ​​based on conditional expressions, similar to the IF-ELSE syntax. Its usage includes: replacing NULL values, converting data types, and returning different values ​​based on conditions.

Usage of DECODE function in SQL

Overview of DECODE function

Syntax

<code class="sql">DECODE(expression, value1, result1, value2, result2, ..., default_result)</code>

Parameters

• expression: The expression to evaluate.
• value1, value2, ...: The condition value to be checked.
• result1, result2, ...: The result value returned when matching the corresponding condition value.
• default_result: The default result value returned if the expression does not match any condition value.

Usage

• Replace NULL value:You can use DECODE The function replaces the NULL value with the specified value, for example:

<code class="sql">SELECT DECODE(name, NULL, 'Unknown', name) FROM customers;</code>

• Convert data type: You can use the DECODE function to convert data from one type to another, For example:

<code class="sql">SELECT DECODE(age, NULL, 0, age) FROM employees;</code>

• Return different values ​​​​according to conditions: You can use the DECODE function to return different values ​​​​according to conditions, for example:

<code class="sql">SELECT DECODE(gender, 'M', 'Male', 'F', 'Female', 'Other') FROM students;</code>

Example

<code class="sql">CREATE TABLE sales (
  product_id INT,
  product_name VARCHAR(50),
  category VARCHAR(20),
  sales_amount DECIMAL(10, 2)
);

INSERT INTO sales (product_id, product_name, category, sales_amount) VALUES
(1, 'Smart TV', 'Electronics', 2000.00),
(2, 'iPhone', 'Electronics', 1500.00),
(3, 'Coffee Maker', 'Appliances', 300.00),
(4, 'Microwave', 'Appliances', 450.00);

SELECT
  product_name,
  category,
  DECODE(sales_amount,
    NULL, 'No Sales',
    2000.00, 'High Sales',
    1500.00, 'Medium Sales',
    'Low Sales') AS sales_category
FROM sales;</code>

Output:

<code>product_name | category | sales_category
Smart TV      | Electronics | High Sales
iPhone        | Electronics | Medium Sales
Coffee Maker  | Appliances | Low Sales
Microwave     | Appliances | Low Sales</code>

The above is the detailed content of Usage of decode in sql. For more information, please follow other related articles on the PHP Chinese website!