Assign int values to specific strings

Question

Consider the following simple MySQL table:

| Answer           |
| ---------------- |
| Extremely likely |
| Likely           |
| Likely           |
| Not a chance     |
| Likely           |
| Potato           |
| Unlikely         |

I would like to write a query to SELECT from this table, which replaces any occurrences of 'Extremely likely' with 1, 'Likely' with 2, 'Unlikely' with 3 and 'Not a chance' with 4.

All other answers should not be converted.

So the result would be:

| Answer |
| ------ |
| 1      |
| 2      |
| 2      |
| 4      |
| 2      |
| Potato |
| 3      |

Can this be done with SQL alone?

After this question was answered I realised I also needed to only do this when another field had a particular value. Here is such a table:

| QuestionID | Answer           |
| ---------- | ---------------- |
| how_likely | Extremely likely |
| how_likely | Likely           |
| how_likely | Likely           |
| fave_veg   | Potato           |

The query I successfully used for this was:

SELECT QuestionID,
    IF(
        QuestionID = 'how_likely',
        CASE Answer
            WHEN 'Extremely likely' THEN 1
            WHEN 'Likely'           THEN 2
            WHEN 'Unlikely'         THEN 3
            WHEN 'Not a chance'     THEN 4
        END,
        Answer
    ) AS Answer
FROM `answers`

gvee · Accepted Answer · 2013-08-05 13:39:43Z

4

SELECT CASE answer
         WHEN 'Extremely Likely' THEN 1
         WHEN 'Likely'           THEN 2
         WHEN 'Unlikely'         THEN 3
         WHEN 'Not a chance'     THEN 4
         ELSE answer
       END As new_answer
FROM   your_table

EDIT: If you have lots of [dynamic] replacements to do then you may find this method too cumbersome to maintain. In these instances you should create a lookup table which you can perform an OUTER JOIN to:

SELECT Coalesce(lookup_values.new_answer, your_table.answer) As new_answer
FROM   your_table
 LEFT
  JOIN lookup_values
    ON lookup_values.answer = your_table.answer

edited Aug 5, 2013 at 13:39

answered Aug 5, 2013 at 13:03

gvee

17.2k42 silver badges50 bronze badges

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

1 Comment

tomturton Over a year ago

Lookup table is not a bad idea. I will look into that, thanks.

Kuzgun · Accepted Answer · 2013-08-05 13:03:37Z

0

REPLACE(REPLACE(REPLACE(REPLACE(Answer, 'Extremely likely', '1') , 'Likely', '2') , 'Unlikely', '3') , 'Not a chance', '4')

answered Aug 5, 2013 at 13:03

Kuzgun

4,7575 gold badges37 silver badges50 bronze badges

2 Comments

Vatev Over a year ago

What if there is a value like 'Unlikely, but only on mondays'?

Kuzgun Over a year ago

There would be no problem if you put it in inner paranthese before 'Unlikely' so it will be already replaced with some number and 'Unlikely' wont effect

Collectives™ on Stack Overflow

Assign int values to specific strings

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related