CODEWITHSUNDEEP
mysqlselectenums
faulty
faulty

Reputation: 8347

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

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

Upvotes: 4

Views: 6142

Answers (1)

The Scrum Meister
The Scrum Meister

Reputation: 30111

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

Upvotes: 10

Related Questions