How to output enum/string when selecting from a int column in MySQL

Question

I'm using view to do most of my reports for data on MySQL. In one of the table, I would like to treat a particular column like ENUM when selecting data, but it's currently int.

I knew it's possible to use inner join to accomplish this, but I'll keep that as my last option.

Is there a way to build the view or select query so that i can sort of cast int into enum and have string output instead of int? Something like

SELECT CONVERT(int_column, ENUM('A', 'B', 'C')) FROM table;

I need to store that column as int, changing the column to enum will require too many changes at the application level, and this is only needed for one particular report, so should be fine. The enum will have 11 values only.

If casting is not possible, then can tertiary operator or inline if works in select?

Thanks

The Scrum Meister · Accepted Answer · 2010-12-03 05:29:40Z

10

Use the ELT() Function

   SELECT ELT(int_column, 'A', 'B', 'C') FROM table;

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_elt

answered Dec 3, 2010 at 5:29

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

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

1 Comment

user3827510 Over a year ago

Note that for zero-indexed columns, you can just do SELECT ELT(int_column + 1, 'A', 'B', 'C') FROM table;

Collectives™ on Stack Overflow

How to output enum/string when selecting from a int column in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related