Usage of decode function in mysql

DECODE function maps values ​​based on a given condition: evaluates an expression and matches the condition value. If there is a match, the corresponding result value is returned; if there is no match, the default value is returned.

Usage of DECODE function in MySQL

The DECODE function is a very useful function that allows you to Mapping one value to another value under certain conditions. Its syntax is as follows:

<code>DECODE(expression, value1, result1, value2, result2, ..., default_result)</code>

where:

• expression is the expression to be evaluated.
• value1, value2, ... are the condition values ​​to be checked.
• result1, result2, ... are the result values ​​corresponding to each condition value.
• default_result is the default result value when the expression does not match any condition value.

Usage

To use the DECODE function, you need to use the following steps:

1. Determine the expression and evaluate its value.
2. Check whether the expression value matches any condition value.
3. If matched, return the result value corresponding to the condition value.
4. If there is no match, return the default result value.

Example

The following example maps the gender value to the word "male" or "female":

<code class="sql">SELECT DECODE(gender, 'M', 'male', 'F', 'female', 'unknown') FROM table_name;</code>

Output:

<code>| gender | result |
|---|---|
| M      | male   |
| F      | female |
| U      | unknown |</code>

Advantages

The DECODE function has the following advantages:

• Easy to use and understand.
• Can handle complex conditions.
• can be used to implement various data conversions.

Alternatives

In some cases, other functions can be used instead of the DECODE function. These functions include:

• CASE WHEN statement
• IF function
• COALESCE function

Conclusion

The DECODE function is a powerful function that allows you to easily map one value to another. It can be used for a variety of data transformation tasks and is easy to use and understand.

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