A CASE expression specifies a value with a condition.
CASE-expression::={searched-CASE-expression|simple-CASE-expression|
CASE-abbreviation}
searched-CASE-expression::=CASE
{WHEN search-condition THEN {value-expression
|NULL}}...
[ELSE {value-expression|NULL}]
END
simple-CASE-expression::=CASE value-expression
{WHEN value-condition THEN {value-expression
|NULL}}...
[ELSE {value-expression|NULL}]
END
CASE-abbreviation::={NULLIF (value-expression, value-expression)
|COALESCE (value-expression, [, value-expression]...)}
Table 2-85 Character set of the result of a CASE expression
CASE expression | Character set of result | |
---|---|---|
Search CASE expression | Character set of the value expression specified in the first THEN | |
Simple CASE expression | Character set of the value expression specified in the first THEN | |
CASE abbreviation | NULLIF | Character set of the first value expression |
COALESCE | Character set of the first value expression |
CASE WHEN V1=V2 THEN NULL ELSE V1 END
VALUE(V1, V2)
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END
VALUE(V1, V2, ..., Vn)
CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, ..., Vn) END
In table T1, change AA and BB in column C1 to AAA and BBB, respectively:
UPDATE T1 SET C1 =
CASE WHEN C1='AA' THEN 'AAA' WHEN C1='BB' THEN 'BBB'
ELSE C1
END
In table T1, change AA and BB in column C1 to AAA and BBB, respectively:
UPDATE T1 SET C1 =
CASE C1 WHEN 'AA' THEN 'AAA' WHEN 'BB' THEN 'BBB'
ELSE C1
END
Extract columns that do not contain all null values from table T1, in the order of columns C1, C2, and C3; if all columns contain only null values, set 0 as the result:
SELECT COALESCE(C1,C2,C3,0) FROM T1
If the values in columns C1 and C2 of table T1 are equal, the null value is returned as the result; if the values in columns C1 and C2 of table T1 are not equal, column C1 is extracted:
SELECT NULLIF(C1,C2) FROM T1