Mapping values in SQL select?

Question

I have a table, lets for simplicity call it Plant, with three columns: id, name, category.
This the most simplified example, so please do not worry about normalization ...

+-------+--------+------------+
| id    | name   | category   |
+-------+--------+------------+
| 1     | orange | fruits     |
| 2     | banana | fruits     |
| 3     | tomato | vegetables |
| 4     | kaokao | NULL       |
+-------+--------+------------+

If want to have a query which returns:

'Fruit Plant' instead of 'fruits'
'Vegetable Plant' instead of 'vegetables'
'unknown' instead of NULLs

So the return should be:

+-------+--------+-----------------+
| id    | name   | category        |
+-------+--------+-----------------+
| 1     | orange | Fruit Plant     |
| 2     | banana | Fruit Plant     |
| 3     | tomato | Vegetable Plant |
| 4     | kaokao | unknown         |
+-------+--------+-----------------+

How can I do this mapping for select values ?

I am using mysql, if this may have a special IF keyword/function in mysql

Andrey Korneyev · Accepted Answer · 2016-07-25 12:11:17Z

63

You can use case expression:

select
    id,
    name,
    case 
        when category = 'fruits' then 'Fruit Plant'
        when category = 'vegetables' then 'Vegetable Plant'
        when category is null then 'unknown'
    end as category
from Plant

answered Jul 25, 2016 at 12:11

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashraf Bashir Over a year ago

Thanks I did not know that there is a case block in mysql. How can I set a default to it for all other values other than fruits and vegetables and nulls ?

Gordon Linoff Over a year ago

@AshrafBashir . . . use else.

Grace · Accepted Answer · 2020-06-03 01:52:07Z

24

use case function (with else statement for default values) :

select id, name, 
case category
   when 'vegetables' then 'Vegetable Plant'
   when 'fruits' then 'Fruit Plant'
   when is null then 'unknown'
   else 'default values'
end
from Plant

edited Jun 3, 2020 at 1:52

answered Jul 25, 2016 at 12:17

Grace

8981 gold badge12 silver badges21 bronze badges

Collectives™ on Stack Overflow

Mapping values in SQL select?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related