CODEWITHSUNDEEP
mysql
chowwy
chowwy

Reputation: 1126

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)

Upvotes: 0

Views: 3937

Answers (3)

eggyal
eggyal

Reputation: 126025

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

Upvotes: 5

RET
RET

Reputation: 9188

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.

Upvotes: 1

Tamer Shlash
Tamer Shlash

Reputation: 9523

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).

Upvotes: 1

Related Questions