mySQL convert integer to text in SELECT query

Question

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)

Why not create a Languages table that has the ID and the name, then select the name? — gcochard, Apr 30 '12 at 23:22
Thanks, did that. It only pulls the first value in the field. — chowwy, Apr 30 '12 at 23:34

score 5 · Accepted Answer · edited May 23 '17 at 12:06

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

Your code works for the first entry in the field, but doesn't pull the second. Any thoughts on how I can get both values? Also I don't think I could use ENUM, because multiple values could be selected. Finally, I tried the lookup table, but it also only pulled the first value. — chowwy, Apr 30 '12 at 23:33
@chowwy: I had misunderstood your requirements. Please see my updated answer. — eggyal, Apr 30 '12 at 23:36
Thank you for your response. I think you're right. I've accepted and upvoted your answer. — chowwy, May 01 '12 at 00:04

Tamer Shlash · Answer 2 · 2012-04-30T23:29:42.240

1

Why don't you make a number-language table and, when SELECTing, get the language associated with that number that you selected.

This is better in case you want to add a new language. You will only insert it into the table instead of changing all the queries in your code, and also easier if others are using your code (they won't be happy debugging and editing all the queries).

edited Apr 30 '12 at 23:29

answered Apr 30 '12 at 23:24

Tamer Shlash

9,314
5
44
82

Thank you--I tried that; it only pulls the first number in the field. – chowwy Apr 30 '12 at 23:27
1

I didn't actually understand, it might be clearer if you put your query (in the question of course :) ). – Tamer Shlash Apr 30 '12 at 23:35

score 1 · Answer 3 · answered Apr 30 '12 at 23:54

From your other comments, are you saying that the languages field is a literal string embedded with commas?

From an SQL perspective, that's a pretty unworkable design. A variable number of languages should be stored in another table.

However, if you're stuck with what you've got, you might be able to construct a regexp replacement algorithm, but it seems terribly fragile, and I wouldn't recommend it. If you've got more than 9 languages, the following will be broken, and you would need the Regexp UDF, which introduces a bunch of complexity.

Assuming the simple case:

SELECT REPLACE(
            REPLACE(
                REPLACE(Languages, '1', 'English'),
                '2', 'French'),
            N, DESCRIPTION)

and so on. But I repeat: this is an awful data design. If it's possible to fix it to something like:

person            person_lang          language
==========        ============         =========
person_id -----<  person_id
...               lang_id      >-----  lang_id
                                       lang_desc

Then I strongly suggest you do so.

Thanks for this. I'm going to update my database design. And I upvoted your answer! — chowwy, May 01 '12 at 00:06

mySQL convert integer to text in SELECT query

3 Answers3