CODEWITHSUNDEEP
mysql
Lee Loftiss
Lee Loftiss

Reputation: 3195

Return tinyint value as predefied text in MySQL query

I'm attempting to return the value of a tinyint column as a predefined text.

In my dB, I have a column called thing_status which is a tinyint. The values are 0='Empty', 1='Full' and 2='Removed'.

When I do my query, I would like to add an extra entry that contains the text representation of thing_status.

For example:

SELECT
   thing_id,
   thing_status,
   {function to convert status to string} AS thing_status_text

I had 2 ideas already, but neither seemed a good fit:

  1. I could use IF THEN statements, but the actual lists of status texts is about 6 items, so the statement would be quite ugly and nasty to work with.
  2. I could create another table with the statuses. But it seems to me that if this can be done IN the query, then it would be more efficient. Plus another table for 6 things that need to only be used once seems like overkill.

Upvotes: 1

Views: 756

Answers (2)

user2504195
user2504195

Reputation: 1

The easiest way is using the ELT() operator:

SELECT 
thing_id,
thing_status, 
ELT(thing_status, 'Empty', 'Full', 'Removed') AS thing_status_text;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use a case statement:

select thing_id, thing_status,
       (case when thing_status = 0 then 'Empty'
             when thing_status = 1 then 'Full'
             when thing_status = 2 then 'Removed'
       end) as thing_status_text

You can also put this into a view, so it is available to any query that wants to use it.

However, you might also be interested in enum, a data type helpful in this situation.

Upvotes: 2

Related Questions