MariaDB DECODE_ORACLE SQL Function - Compare expressions |
|
| | DECODE_ORACLE | Syntax: | DECODE_ORACLE(Expr, Expr1, Value1, [Expr2, Value2 ...], [Default]) | | Return value: | Depending on the data types used | | Function type: | Comparison function | |
| | The DECODE_ORACLE() function compares expressions and returns the assigned value if they match.
If no match is found, NULL is returned, or "Default" if specified.
NULL values in expressions are also taken into account and can be compared with each other.
This feature is available from MariaDB version 10.3.2. If Oracle mode is on, the function DECODE() is executed like this.
This feature is not present in MySQL 8.0. | SQL Examples for the DECODE_ORACLE function |
1
2
3
4
5
6
7
8
9
10
11
12
|
| select decode_oracle(2, 1, 'val1', 2, 'val2', 'not found');
select decode_oracle(3, 1, 'val1', 2, 'val2', 'not found');
select decode_oracle(3, 1, 'val1', 2, 'val2');
select decode_oracle(2, 1, 5, 2, 5, 0);
select decode_oracle(3, 1, 5, 2, 5, 0);
select decode_oracle(3, 1, 5, 2, 5);
select decode_oracle(null, 1, 'val1', null, 'val2', 'not found');
select decode_oracle(3, 1, 'val1', null, 'val2', 'not found');
|
|
| decode_oracle(2, 1, 'val1', 2, 'val2', 'not found') |
|---|
| varchar(9) BINARY |
|---|
| val2 |
|
|
| decode_oracle(3, 1, 'val1', 2, 'val2', 'not found') |
|---|
| varchar(9) BINARY |
|---|
| not found |
|
|
| decode_oracle(3, 1, 'val1', 2, 'val2') |
|---|
| varchar(4) BINARY |
|---|
| NULL |
|
|
| decode_oracle(2, 1, 5, 2, 5, 0) |
|---|
| int(1) |
|---|
| 5 |
|
|
| decode_oracle(3, 1, 5, 2, 5, 0) |
|---|
| int(1) |
|---|
| 0 |
|
|
| decode_oracle(3, 1, 5, 2, 5) |
|---|
| int(1) |
|---|
| NULL |
|
|
| decode_oracle(null, 1, 'val1', null, 'val2', 'not found') |
|---|
| varchar(9) BINARY |
|---|
| val2 |
|
|
| decode_oracle(3, 1, 'val1', null, 'val2', 'not found') |
|---|
| varchar(9) BINARY |
|---|
| not found |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the DECODE_ORACLE() function in MySQL and MariaDB databases | The DECODE_ORACLE() function in MariaDB is a powerful conditional function that can be used to transform or replace values based on a set of conditions. The function takes an expression as the first argument, followed by a set of search values and their corresponding results, compares the expression to the set of conditions and returns a result based on the first condition that is matched. If no match is found, the default result is returned. The function evaluates all conditions and expressions, even if a match is found.
For example, the function can be used to convert or map one value to another based on specified conditions. Certain values are replaced with other values based on conditions. Data can be standardized or sanitized by replacing inconsistent or invalid values.
This function is called DECODE() in the Oracle database system and is used in MariaDB under the name DECODE_ORACLE() to be able to use it even if the database is not running in Oracle mode. | | Further MySQL und MariaDB SQL Comparison functions | |
| | | More information about the DECODE_ORACLE SQL function: |
|
|
|
|
