sql convert column string to discrete integer numbers

Let's say that I have the colunm name weather (STRING), all it values in fact are limited, for example, we can suppose, it only have five values with AAA, BBB, CCC, DDD, EEE, since I want to do some machine learning algorithms, I want to change the column type to INT,and at the same time, change its values into numbers 1, 2, 3, 4, 5 corresponding to AAA, BBB, CCC, DDD, EEE.

How to write the SQL code ?