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

Answer 1

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