CODEWITHSUNDEEP
mysqlsqlselect
Lai32290
Lai32290

Reputation: 8548

SQL How to replace values of select return?

In my database table (MySQL), there is a column with 1 and 0 to represent true and false respectively.

But in SELECT, I need to replace it for true or false in order to print in a GridView.

How do 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 to show thereby:

  id  |  name    |  hide
  1   |  Paul    |  true
  2   |  John    |  false
  3   |  Jessica |  true

Upvotes: 70

Views: 282805

Answers (8)

idev
idev

Reputation: 9

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)

Upvotes: -2

Gianfranco P
Gianfranco P

Reputation: 10794

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;

Upvotes: 0

A.D.
A.D.

Reputation: 2372

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

Upvotes: 2

Sandeep Kamath
Sandeep Kamath

Reputation: 708

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.

Upvotes: 55

frugal-one
frugal-one

Reputation: 931

You have a number of choices:

  1. Join with a domain table with TRUE, FALSE Boolean value.

  2. 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

Upvotes: 88

jospratik
jospratik

Reputation: 1674

You can use casting in the select clause like:

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

Upvotes: 8

Amilcar Andrade
Amilcar Andrade

Reputation: 1171

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.

Upvotes: 36

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

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.

Upvotes: 25

Related Questions