Sony AK Knowledge Center

Here is the sample of using CASE statement on MySQL. Suppose you have table on MySQL like this.

mysql> desc tbl_employee;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| name          | varchar(100) | NO   |     | NULL    |       |
| gender        | char(1)      | NO   |     | NULL    |       |
| birth_date    | date         | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

And you populate that table with this.

INSERT INTO tbl_employee (name, gender, birth_date) VALUES ('Sony AK', 'M', '1977-02-21');
INSERT INTO tbl_employee (name, gender, birth_date) VALUES ('Esa Ivani', 'F', '1977-03-10');

Now you will have data like these.

mysql> select * from tbl_employee;
+-----------+---------+---------------+
| name      | gender  | birth_date    |
+-----------+---------+---------------+
| Sony AK   | M       | 1977-02-21    |
| Esa Ivani | F       | 1977-03-10    |
+-----------+---------+---------------+
2 rows in set (0.00 sec)

Now we use CASE statement for field gender.

SELECT name, CASE gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS employee_gender, birth_date FROM tbl_employee;

The result will be like this.

mysql> SELECT name, CASE gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END
AS employee_gender, birth_date FROM tbl_employee;
+-----------+-----------------+------------+
| name      | employee_gender | birth_date |
+-----------+-----------------+------------+
| Sony AK   | Male            | 1977-02-21 |
| Esa Ivani | Female          | 1977-03-10 |
+-----------+-----------------+------------+
2 rows in set (0.00 sec)

Or you can do like this also.

SELECT name, CASE WHEN gender = 'M' THEN 'Male' WHEN gender = 'F' THEN 'Female' END AS employee_gender, birth_date FROM tbl_employee;

The result will be the same like above.