Creating a SQL table, how can I convert a BIT entry to text YES or NO?

Question

Very new to SQL and trying to set up a users table, I have a check box for financial aid that outputs a BIT 0 or 1, ideally what I would like to do is convert this value to a YES or NO depending on the value. Right now I'm not entirely sure how to do this, I've tried adding

SELECT CASE `financial_aid`
         WHEN 1 THEN 'Yes' 
         WHEN 0 THEN 'No' 
       END As `financial_aid`
FROM `users`;

Currently this doesn't solve my problem, can anyone advise how I can fix this?

SQL

CREATE TABLE IF NOT EXISTS `users` (
  `User_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `player_name` varchar(40) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `dob` date NOT NULL,
  `parent_name` varchar(40) NOT NULL,
  `parent_email` varchar(40) NOT NULL,
  `parent_phone` varchar(40) NOT NULL,
  `experience` varchar(40) NOT NULL,
  `financial_aid` bit NOT NULL,
  `registration_date` datetime NOT NULL,
  PRIMARY KEY (`User_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10;

SELECT CASE `financial_aid`
          WHEN 1 THEN 'Yes'
          WHEN 0 THEN 'No' 
       END As `financial_aid` 
FROM `users`;

`Currently this doesn't solve my problem` - it gives an error? — ethrbunny, Jan 06 '14 at 12:52
hey, currently it just registers as either 00000001 or 00000000 in my database instead of Yes or No — styler, Jan 06 '14 at 12:56
Your query works fine : http://www.sqlfiddle.com/#!2/f4ae2/2 — StuartLC, Jan 06 '14 at 12:57
Please elaborate more on why this doesn't solve your problem. Are you actually asking for a statement that will change the contents of the table? — MatBailie, Jan 06 '14 at 12:59
Hey, yeah I would like my financial_aid column to display Yes or No, at the moment I just see the following: http://d.pr/i/uGyO — styler, Jan 06 '14 at 13:04
If you want this persisted in the table, then you'll probably need a new column and a synchronizing [trigger](http://stackoverflow.com/questions/5222044/column-calculated-from-another-column) (MySql doesn't support Computed columns). Otherwise, just modify the query or presentation tier (e.g. screen) so that it maps it as you've done with the `case ... when` — StuartLC, Jan 06 '14 at 13:42

Ravinder Reddy · Answer 1 · 2014-01-06T13:01:05.260

2

Try IF(true, x, y) function ...

SELECT IF(`financial_aid`, 'Yes', 'No') As `financial_aid` FROM `users`;

Or simply

SELECT case `financial_aid`
         WHEN TRUE 'Yes'
         ELSE 'No' END
        As `financial_aid`
FROM `users`;

edited Jan 06 '14 at 13:01

answered Jan 06 '14 at 12:56

Ravinder Reddy

23,692
6
52
82

Creating a SQL table, how can I convert a BIT entry to text YES or NO?

1 Answers1