SQL How to replace values of select return?
MysqlSqlSelectMysql Problem Overview
In my database (MySQL) table, has a column with 1
and 0
for represent true
and false
respectively.
But in SELECT
, I need it replace for true
or false
for printing in a GridView.
How to I make my SELECT
query to do this?
In my current table:
id | name | hide
1 | Paul | 1
2 | John | 0
3 | Jessica | 1
I need it show thereby:
id | name | hide
1 | Paul | true
2 | John | false
3 | Jessica | true
Mysql Solutions
Solution 1 - Mysql
You have a number of choices:
-
Join with a domain table with
TRUE
,FALSE
Boolean value. -
Use (as pointed in this answer)
SELECT CASE WHEN hide = 0 THEN FALSE ELSE TRUE END FROM
Or if Boolean is not supported:
SELECT CASE WHEN hide = 0 THEN 'false' ELSE 'true' END FROM
Solution 2 - Mysql
I got the solution
SELECT
CASE status
WHEN 'VS' THEN 'validated by subsidiary'
WHEN 'NA' THEN 'not acceptable'
WHEN 'D' THEN 'delisted'
ELSE 'validated'
END AS STATUS
FROM SUPP_STATUS
This is using the CASE This is another to manipulate the selected value for more that two options.
Solution 3 - Mysql
You can do something like this:
SELECT id,name, REPLACE(REPLACE(hide,0,"false"),1,"true") AS hide FROM your-table
Hope this can help you.
Solution 4 - Mysql
If you want the column as string values, then:
SELECT id, name, CASE WHEN hide = 0 THEN 'false' ELSE 'true' END AS hide
FROM anonymous_table
If the DBMS supports BOOLEAN, you can use instead:
SELECT id, name, CASE WHEN hide = 0 THEN false ELSE true END AS hide
FROM anonymous_table
That's the same except that the quotes around the names false
and true
were removed.
Solution 5 - Mysql
You can use casting in the select clause like:
SELECT id, name, CAST(hide AS BOOLEAN) FROM table_name;
Solution 6 - Mysql
I saying that the case statement is wrong but this can be a good solution instead.
If you choose to use the CASE
statement, you have to make sure that at least one of the CASE
condition is matched. Otherwise, you need to define an error handler to catch the error. Recall that you don’t have to do this with the IF
statement.
SELECT if(hide = 0,FALSE,TRUE) col FROM tbl; #for BOOLEAN Value return
or
SELECT if(hide = 0,'FALSE','TRUE') col FROM tbl; #for string Value return
Solution 7 - Mysql
in Postgres 11 I had to do this:
type
is an int
SELECT type,
CASE
WHEN type = 1 THEN 'todo'
ELSE 'event'
END as type_s
from calendar_items;
Solution 8 - Mysql
Replace the value in select statement itself...
(CASE WHEN Mobile LIKE '966%' THEN (select REPLACE(CAST(Mobile AS nvarchar(MAX)),'966','0')) ELSE Mobile END)