SQL How to replace values of select return?

Question

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

Please remember that tables have names — we can invent names for you, but it gives the answers greater consistency if you tell us the name of the table. It is also a good idea to specify which DBMS you're using since there are variations between the different DBMS. — Jonathan Leffler, May 25 '13 at 19:36

score 85 · Accepted Answer · edited May 23 '17 at 12:18

85

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

edited May 23 '17 at 12:18

Community

1
1

answered May 25 '13 at 19:28

frugal-one

901
7
2

score 51 · Answer 2 · answered Jun 02 '17 at 12:58

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.

score 33 · Answer 3 · answered May 25 '13 at 19:43

33

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.

answered May 25 '13 at 19:43

Amilcar Andrade

1,131
9
16

While it works given the REPLACE function (which DBMS has that?), the double application is not all that elegant, and doesn't extend to 3 or more cases very well, does it? It is an alternative answer, though. – Jonathan Leffler May 25 '13 at 19:51
I just tried it with MySQL and you are right is not very good approach if you have more than 3 cases, but in this case this solution works as you mention. :) thanks for commenting – Amilcar Andrade May 25 '13 at 19:57
1

I actually like this approach for a 2 different values and it's a bit easier to look at in the select statement than CASE/WHEN block. – a lead alcove Jul 17 '20 at 15:20
While this works, it's a hack. Replace is a string substitution facility. Suppose something changes and the string "01" comes through instead of 1. This will be converted to "falsetrue". It's better to have some explicit matching of the whole variable. – Rich Farmbrough Jun 14 '21 at 19:03

score 24 · Answer 4 · answered May 25 '13 at 19:30

24

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.

answered May 25 '13 at 19:30

Jonathan Leffler

730,956
141
904
1,278

Mysql 5.7.19-0ubuntu0.16.04.1 - To get `As hide` to work, I had to put parentheses around `CASE ... END`. So: `SELECT id, name, (CASE WHEN ... END) AS hide FROM ...` – ToolmakerSteve Aug 16 '17 at 03:24

jospratik · Answer 5 · 2013-05-25T19:51:59.700

8

You can use casting in the select clause like:

SELECT id, name, CAST(hide AS BOOLEAN) FROM table_name;

edited May 25 '13 at 19:51

answered May 25 '13 at 19:33

jospratik

1,564
11
19

score 2 · Answer 6 · answered Dec 28 '17 at 04:39

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

score 0 · Answer 7 · answered Aug 26 '21 at 11:15

0

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;

answered Aug 26 '21 at 11:15

Gianfranco P.

10,049
6
51
68

score -2 · Answer 8 · edited Nov 29 '22 at 10:56

-2

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)

edited Nov 29 '22 at 10:56

Ilya

1
5
18

answered Mar 28 '18 at 09:59

idev

9
2

You have to tell the reason why your answer will work ! – Jeremy M. Mar 28 '18 at 10:03

SQL How to replace values of select return?

8 Answers8

Linked

Related