Can I use SQL to display custom text that is based on the column value?

Question

Is there a way to show different strings based on value found in a column?

i.e.

SELECT value FROM table;

+--------+
| value  |
+--------+
|      1 |
|      0 |
|      1 |
|      1 |
+--------+

The output I want is this:

+--------+
| value  |
+--------+
|    yes |
|     no |
|    yes |
|    yes |
+--------+

How?

Answer 1

A fun way to do this uses elt():

select elt(value + 1, 'no', 'yes')

elt() returns the nth string based on the first argument.

Answer 2

Using CASE statement you can get the expected result:

SELECT CASE WHEN value = 1 THEN 'yes' 
            WHEN value = 0 THEN 'no' 
            ELSE '' 
       END AS value
FROM testtable;

or using IF statement

SELECT IF(value = 1, 'yes', IF(value = 0, 'no', '')) AS value
FROM testtable;

Demo on db<>fiddle