Converting an integer column to a character in mySQL

Question

My users table currently has a field sex which is either 1 or 2. I want to convert those values to "F" or "M" and put them into a different column. Is there any way to do this in mySQL?

I can write Ruby code to do this in the console, but I have on the order of 100K users and don't want to stall production while I loop.

Do you mean you want to add an extra column to the table or do you just want to get F/M when you query the existing table. — PaulF
– PaulF, Commented Dec 2, 2015 at 13:57
I have a new column that should contain F/M while the original column contains 1/2 — rainslg
– rainslg, Commented Dec 2, 2015 at 14:38

Abhik Chakraborty · Accepted Answer · 2015-12-02 13:59:40Z

1

You can do it in 3 steps

step 1 Add a new column into the table

alter table table_name add column sex_n char(1);

step 2 Write an update query

update table_name
set sex_n = case when sex = 1 then 'M' else 'F' end ;

step 3 Verify if the data is populated correctly then do

alter table table_name drop column sex
alter table table_name change sex_n sex char(1);

If you do not want an extra column you can use select to convert as

select
case when sex=1 then 'M'
else 'F'
end as sex 
from table_name

answered Dec 2, 2015 at 13:59

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

2 Comments

rainslg Over a year ago

Does the latter work if the sex column is type integer?

Abhik Chakraborty Over a year ago

The last one is if you do not want to add another column into the table and using select you can modify and display, but the column will still contain 1 or 2 but while display via query it will show M or F

Pathik Vejani · Accepted Answer · 2015-12-02 13:57:13Z

0

Query:

UPDATE tbl_name SET sex = (CASE WHEN sex = 1 THEN 'M' WHEN sex = 2 THEN 'F' ELSE '' END);

Add extra column with name gender then:

UPDATE tbl_name SET gender = (CASE WHEN sex = 1 THEN 'M' WHEN sex = 2 THEN 'F' ELSE '' END);

answered Dec 2, 2015 at 13:57

Pathik Vejani

4,49711 gold badges66 silver badges110 bronze badges

Collectives™ on Stack Overflow

Converting an integer column to a character in mySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related