Mysql CASE
From w3cyberlearnings
Contents |
MySQL CASE Function
This operator uses to check the case or the condition of the statement and return result accordingly.
Syntax CASE
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
Example 1
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-> WHEN 2 THEN 'two'
-> END AS 'tipcase';
+---------+
| tipcase |
+---------+
| one |
+---------+
1 row in set (0.00 sec)
Example 2
List of Records
mysql> select * from user; +---------+------------------------------+---------------+ | user_id | loginEmail | password | +---------+------------------------------+---------------+ | 1 | admin@w3cyberlearning | d4Z6Jm1L5KLY6 | | 2 | user@w3cyberlearning | d4yOXqnMb09iw | | 3 | sophal@w3cyberlearning | d4elKQV.j6LcQ | | 4 | yiling@w3cyberlearning | d4TBKG4h5SUkY | | 5 | yiling1@w3cyberlearning | d4J/AM81hPu5. | | 6 | kwan@w3cyberlearning | d4Z.cnO8IUAoA | | 7 | kwan1@w3cyberlearning | d4NthJ11wK0KQ | | 8 | kwan1@w3cyberlearning | d4NthJ11wK0KQ | | 9 | kwan1@w3cyberlearning | d4NthJ11wK0KQ | | 10 | wu@w3cyberlearning | d4yk8ErVmdF1w | | 11 | wu@w3cyberlearning | d4yk8ErVmdF1w | | 12 | | d4rBwtAKocqwA | | 13 | wu@w3cyberlearning | d4yk8ErVmdF1w | | 14 | [email protected] | d4D9O7zfTlRZE | | 15 | | d4rBwtAKocqwA | | 16 | [email protected] | 82hngl1f0jsxA | | 17 | [email protected] | 82VgBddnErejI | | 18 | | d4rBwtAKocqwA | +---------+------------------------------+---------------+ 18 rows in set (0.03 sec)
Use CASE In SELECT STATEMENT
Return based on odd or even of the user_id.
mysql> SELECT CASE user_id%2 WHEN 0 THEN 'g' WHEN !0 THEN 'h' END as tip FROM user; +------+ | tip | +------+ | h | | g | | h | | g | | h | | g | | h | | g | | h | | g | | h | | g | | h | | g | | h | | g | | h | | g | +------+ 18 rows in set (0.00 sec)
