chowwy chowwy - 1 year ago 132
MySQL Question

mySQL convert integer to text in SELECT query

I want to convert an integer to text in a mySQL select query. Here's what a table looks like:


I want to convert each integer to a language (e.g., 1 => English, 2 => French, etc.)

I've been reading up on CONVERT and CAST functions in mySQL, but they mostly seem to focus on converting various data types to integers. And also I couldn't find anything that dealt with the specific way I'm storing the data (multiple numbers in one field).

How can I convert the integers to text in a mySQL query?


Here's my mySQL query:

SELECT, ulp.userid, ulp.languages,, ll.language_detail
FROM users AS u
JOIN user_language_profile AS ulp ON (ulp.userid =
JOIN language_detail AS ll ON (ulp.languages =

Answer Source

Use either:

  • MySQL's ELT() funtion:

         , 'English' -- 1
         , 'French'  -- 2
         -- etc.
    FROM   table_name
  • A CASE expression:

      CASE Languages
        WHEN 1 THEN 'English'
        WHEN 2 THEN 'French'
        -- etc.
    FROM table_name

Although, if possible I would be tempted to either JOIN with a lookup table (as @Mr.TAMER says) or change the data type of the column to ENUM('English','French',...).


From your comments, it now seems that each field contains a set (perhaps even using the SET data type?) of languages and you want to replace the numeric values with strings?

First, read Bill Karwin's excellent answer to "Is storing a delimited list in a database column really that bad?".

In this case, I suggest you normalise your database a tad: create a new language-entity table wherein each record associates the PK of the entities in the existing table with a single language. Then you can use a SELECT query (joining on that new table) with GROUP_CONCAT aggregation to obtain the desired list of language names.

Without such normalisation, your only option is to do string-based search & replace (which would not be particularly efficient); for example:

  IF(FIND_IN_SET('1', Languages), 'English', NULL),
  IF(FIND_IN_SET('2', Languages), 'French' , NULL),
  -- etc.
FROM table_name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download