How to apply the IF ELSE condition to the selection of a column?

8

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

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

Example:

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

How can I apply a IF ELSE condition in the selection of this column, indicating the desired value for each of the existing options?

    
asked by anonymous 17.12.2013 / 13:42

1 answer

9

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

Example query:

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

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

Example of the result:

┌────────────────────┐
│  gender            │
└────────────────────┘
┌────────────────────┐
│  undifferentiated  │
├────────────────────┤
│  undifferentiated  │
├────────────────────┤
│  male              │
├────────────────────┤
│  female            │
└────────────────────┘
    
17.12.2013 / 13:42