I want to convert an integer to text in a mySQL select query. Here's what a table looks like:
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)
SELECT ELT(Languages , 'English' -- 1 , 'French' -- 2 -- etc. ) FROM table_name
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
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