©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMySQLMariaDB

MariaDB DECODE_ORACLE SQL Function - Compare expressions

DECODE_ORACLESyntax:DECODE_ORACLE(Expr, Expr1, Value1, [Expr2, Value2 ...], [Default])
Return value:Depending on the data types used
Function type:Comparison function
Function Description

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


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
decode_oracle(3, 1, 'val1', 2, 'val2', 'not found')
varchar(9) BINARY
not found
decode_oracle(3, 1, 'val1', 2, 'val2')
varchar(4) BINARY
decode_oracle(2, 1, 5, 2, 5, 0)
decode_oracle(3, 1, 5, 2, 5, 0)
decode_oracle(3, 1, 5, 2, 5)
decode_oracle(null, 1, 'val1', null, 'val2', 'not found')
varchar(9) BINARY
decode_oracle(3, 1, 'val1', null, 'val2', 'not found')
varchar(9) BINARY
not found
The examples were created with the MyWAY SQL manager: Download

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 about COALESCE Function

More about GREATEST Function

More about INTERVAL Function

IFIF(Expression, Value1, Value2)
More about IF Function

More about LEAST Function

STRCMPSTRCMP(String 1, String 2)
More about STRCMP Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the DECODE_ORACLE SQL function: mariadb.com
Updated: 2023-09-17ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2023 Heino Cunze-Fischer