Set custom messages for enum values in MySQL

Use the if else to set custom messages for enum. Let us first create a table −

mysql> create table DemoTable1992
(
   ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ClientName varchar(20),
   isActive ENUM('Y','N')
);
Query OK, 0 rows affected (0.89 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1992(ClientName,isActive) values('Chris','N');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1992(ClientName,isActive) values('Bob','N');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable1992(ClientName,isActive) values('David','Y');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1992(ClientName,isActive) values('Carol','N');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1992;

This will produce the following output −

+----------+------------+----------+
| ClientId | ClientName | isActive |
+----------+------------+----------+
|        1 | Chris      | N        |
|        2 | Bob        | N        |
|        3 | David      | Y        |
|        4 | Carol      | N        |
+----------+------------+----------+
4 rows in set (0.00 sec)

Here is the query to set custom messages with if else

mysql> select ClientName,if(isActive='Y','Active Member','Not Active Member') as Result from DemoTable1992;

This will produce the following output −

+------------+-------------------+
| ClientName | Result            |
+------------+-------------------+
| Chris      | Not Active Member |
| Bob        | Not Active Member |
| David      | Active Member     |
| Carol      | Not Active Member |
+------------+-------------------+
4 rows in set (0.00 sec)

AmitDiwan

Published on 02-Jan-2020 05:23:47

Previous Page Print Page