chowwy chowwy - 2 months ago 7
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:

Languages
--------
1,2,3


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?

UPDATE

Here's my mySQL query:

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

Answer

Use either:

  • MySQL's ELT() funtion:

    SELECT
      ELT(Languages
         , 'English' -- 1
         , 'French'  -- 2
         -- etc.
      )
    FROM   table_name
    
  • A CASE expression:

    SELECT
      CASE Languages
        WHEN 1 THEN 'English'
        WHEN 2 THEN 'French'
        -- etc.
      END
    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',...).


UPDATE

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:

SELECT CONCAT_WS(',',
  IF(FIND_IN_SET('1', Languages), 'English', NULL),
  IF(FIND_IN_SET('2', Languages), 'French' , NULL),
  -- etc.
)
FROM table_name
Comments