How to replace values of select return in MySQL?

You can use select case statement for this. The syntax is as follows.

select yourColumnName1,yourColumnName2,...N,
case when yourColumnName=1 then 'true'
else 'false'
end as anyVariableName
from yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows.

mysql> create table selectReturnDemo
-> (
-> Id int,
-> Name varchar(100),
-> isGreaterthan18 tinyint(1)
-> );
Query OK, 0 rows affected (0.62 sec)

Now you can insert some records in the table using insert command. The query is as follows.

mysql> insert into selectReturnDemo values(1,'Carol',0);
Query OK, 1 row affected (0.23 sec)

mysql> insert into selectReturnDemo values(2,'Bob',1);
Query OK, 1 row affected (0.21 sec)

mysql> insert into selectReturnDemo values(3,'Mike',1);
Query OK, 1 row affected (0.18 sec)

mysql> insert into selectReturnDemo values(4,'David',0);
Query OK, 1 row affected (0.21 sec)

mysql> insert into selectReturnDemo values(5,'Adam',1);
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement. The query is as follows.

mysql> select *from selectReturnDemo;

The following is the output.

+------+-------+-----------------+
| Id   | Name  | isGreaterthan18 |
+------+-------+-----------------+
| 1    | Carol | 0               |
| 2    | Bob   | 1               |
| 3    | Mike  | 1               |
| 4    | David | 0               |
| 5    | Adam  | 1               |
+------+-------+-----------------+
5 rows in set (0.00 sec)

Here is the query to replace value with select return. The query is as follows.

mysql> select Id,Name,
-> case when isGreaterthan18=1 then 'true'
-> else 'false'
-> end as AgeIsGreaterthan18
-> from selectReturnDemo;

The following is the output.

+------+-------+--------------------+
| Id   | Name  | AgeIsGreaterthan18 |
+------+-------+--------------------+
| 1    | Carol | false              |
| 2    | Bob   | true               |
| 3    | Mike  | true               |
| 4    | David | false              |
| 5    | Adam  | true               |
+------+-------+--------------------+
5 rows in set (0.00 sec)

George John

Updated on: 2019-07-30T22:30:24+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started