CASE
Declaration
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
OR
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
Description
In the returned result of the first case, value= compare-value. The prerequisite of the returned result of the second case is that the first condition is true. If no matching result is found, the result after ELSE is returned. If the ELSE part does not exist, the return value is NULL.
Example
obclient> select CASE 'b' when 'a' then 1 when 'b' then 2 END;
+----------------------------------------------+
| CASE 'b' when 'a' then 1 when 'b' then 2 END |
+----------------------------------------------+
| 2 |
+----------------------------------------------+
1 row in set (0.01 sec)
obclient> select CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end;
+--------------------------------------------------------------------------+
| CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end |
+--------------------------------------------------------------------------+
| a |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
obclient> select case when 1>0 then 'true' else 'false' end;
+--------------------------------------------+
| case when 1>0 then 'true' else 'false' end |
+--------------------------------------------+
| true |
+--------------------------------------------+
1 row in set (0.00 sec)
IF
Declaration
IF(expr1,expr2,expr3)
Description
If the value of expr1 is TRUE (expr1<>0 and expr1<>NULL), the return result is expr2. Otherwise, the return result is expr3.
The return result of IF() can be a numeric value or string value, depending on the content.
If only one of expr2 and expr3 is NULL, the result of IF() is a non-NULL expression.
Example
obclient> select if(5>6, 'T','F'), if (5>6, 1, 0), if(null, 'True', 'False'), if(0, 'True', 'False')\G
*************************** 1. row ***************************
if(5>6, 'T','F'): F
if (5>6, 1, 0): 0
if(null, 'True', 'False'): False
if(0, 'True', 'False'): False
1 row in set (0.01 sec)
IFNULL
Declaration
IFNULL(expr1, expr2)
Description
Assume that expr1 is not NULL. The return value of IFNULL() is expr1. Otherwise, its return value is expr2. The return value of IFNULL() is a numeric value or a string, depending on the context.
The default return value type of IFNULL() is determined based on the following rules.
| Expression | Return value |
|---|---|
The return value of expr1 or expr2 is a string. |
String |
The return value of expr1 or expr2 is a floating-point value. |
Floating point |
The return value of expr1 or expr2 is an integer. |
Integer |
If both expr1 and expr2 are strings, and either of them is case-sensitive, the return result is case-sensitive.
Example
obclient> SELECT IFNULL('abc', null), IFNULL(NULL+1, NULL+2), IFNULL(1/0, 0/1);
+---------------------+------------------------+------------------+
| IFNULL('abc', null) | IFNULL(NULL+1, NULL+2) | IFNULL(1/0, 0/1) |
+---------------------+------------------------+------------------+
| abc | NULL | 0.0000 |
+---------------------+------------------------+------------------+
1 row in set (0.01 sec)
NULLIF
Declaration
NULLIF(expr1, expr2)
Description
If expr1 = expr2, the return value is NULL. Otherwise, the return value is expr1. It is the same as CASE WHEN
expr1 = expr2 THEN NULL ELSE expr1 END. Note that if the parameters are not equal, the value of the two calculations is expr1.
Example
obclient> SELECT NULLIF('ABC', 123), NULLIF('123',123), NULLIF(NULL, 'abc');
+--------------------+-------------------+---------------------+
| NULLIF('ABC', 123) | NULLIF('123',123) | NULLIF(NULL, 'abc') |
+--------------------+-------------------+---------------------+
| ABC | NULL | NULL |
+--------------------+-------------------+---------------------+
1 row in set, 1 warning (0.00 sec)
ORA_DECODE
Declaration
ORA_DECODE (condition, value 1, return value 1, value 2, return value 2, ... value n, return value n, default value)
Description
The functionality of ORA_DECODE() is the same as that of the DECODE() function in Oracle database.
Mechanism of this function:
IF condition = value 1
THEN RETURN (return value 1)
ELSIF condition = value 2
THEN RETURN (return value 2)
......
ELSIF condition = value n
THEN RETURN (return value n)
ELSE RETURN (default value)
END IF