Name
CASE
Synopsis
CASEvalue
WHEN [value
] THENresult
. . . [ELSEresult
] END CASE WHEN [condition
] THENresult
. . . [ELSEresult
] END
This function produces results that vary based on which
condition
is true. It is similar to the
IF() function, except that multiple
conditions and results may be strung together. In the first syntax
shown, the value
given after
CASE
is compared to each WHEN
value. If a match is found, the result
given for the THEN
is returned. The second syntax
tests each condition independently, and they are not based on a single
value. For both syntaxes, if no match is found and an
ELSE
clause is included, the result given for the
ELSE
clause is returned. If there is no match and
no ELSE
clause is given, NULL is returned.
If the chosen result
is a string, it
is returned as a string data type. If
result
is numeric, the result may be
returned as a decimal, real, or integer value.
Here’s an example of the first syntax shown:
SELECT CONCAT(name_first, SPACE(1), name_last) AS Client, telephone_home AS Telephone, CASE type WHEN 'RET' THEN 'Retirement Account' WHEN 'REG' THEN 'Regular Account' WHEN 'CUS' THEN 'Minor Account' END AS 'Account Type' FROM clients;
This SQL statement retrieves a list of clients and their
telephone numbers, along with a description of their account types.
However, the account type is a three-letter abbreviation, so
CASE()
is used to substitute each type with a
more descriptive name.
This example uses the syntax in which a common parameter is evaluated ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.