mysql – How to apply IF ELSE condition to a column selection?

Question:

In MySQL, I have an old entities table where the gender column is in ENUM format, containing values ​​of type: "i", "m", "f".

When migrating this data to the new structure of this table, I wanted to execute a query to the database, collecting the values ​​as they will be in the destination table:

Example:

┌─────────────┬────────────────────┐
│ Valor atual │  Valor pretendido  │
├─────────────┼────────────────────┤
│      i      │  undifferentiated  │
├─────────────┼────────────────────┤
│      m      │  male              │
├─────────────┼────────────────────┤
│      f      │  female            │
└─────────────┴────────────────────┘

How can I apply an IF ELSE condition on the selection of this column, indicating the intended value for each of the existing options?

Answer:

One way is to apply the CASE operator to the selection of the desired column indicating each of the "conversions" to take place:

Query example:

SELECT CASE gender
  WHEN 'i' THEN 'undifferentiated'
  WHEN 'm' THEN 'male'
  WHEN 'f' THEN 'female'
END AS gender
FROM `entities`
WHERE 1

What is being done is to select the gender column of all the rows of the entities table, where for each row we check its value and assign the new desired value.

Result example:

┌────────────────────┐
│  gender            │
└────────────────────┘
┌────────────────────┐
│  undifferentiated  │
├────────────────────┤
│  undifferentiated  │
├────────────────────┤
│  male              │
├────────────────────┤
│  female            │
└────────────────────┘