How to convert integer value stored in a database table to human readable format in SQL query?-CodePudding

I have a column in my SQL table. The value stored in this column is integer values for example 1,2,3,4,8 etc. Each value represents something else, such as 1=volvo, 2=BMW, 3=Ford etc. Is there a way to run a query where the results is translated from binary to text?

Current query:

SELECT * 
FROM table carModel

Result:

1, green, hatchback

Desired result:

Volvo, green, hatchback

CodePudding user response：

One solution is as follow: Your table carModel lets say it has the following results:

id	color	model
1	green	Volvo
2	red	BMW
3	purple	Mercedes-benz

One more table called carTypes

id	model_id	type
1	1	Sedan
2	2	Coupe
3	3	Hatchback

In carTypes model_id is pointing to carModel id

And the very very simple INNER JOIN will do the job.

SELECT `CM`.`model`, `CM`.`color`, `CT`.`type`,
FROM `carModel` as `CM`
INNER JOIN `carTypes` as `CT` ON `CT`.`model_id ` = `CM`.`id`;

... as I wrote that is very simple example. You will probably need to read about how to link two tables, about Foreign key, how to create lookup and also not in last but how to populate in order to get right data. Of course if you don't know that already.

And keep in mind such a query like above will return you all data. Perhaps, you need to add WHERE clause in order to have more narrowed results.

Hope that helps you at least a bit.

CodePudding user response：

I assume that the CarModel table includes the colums brandId, bolor, modelName

Create a table named "Brands" with Id column and brandName and Insert the brands in this table.

Then the below query should work:

SELECT b.brandName, cm.Color, cm.modelName
FROM carModel cm LEFT JOIN Brands b ON cm.brandId = b.Id

CodePudding user response：

If you don't have the mapping table, you can create one using with clause statement as below -

with car_model_map as (select 1 as model_id, 'volvo' as model_name
                       union
                       select 2, 'BMW'
                       union
                       select 3, 'Ford')
SELECT cm.model_name, ca.color, ca.model_type
FROM carModel ca join car_model_map cm on ca.model_id = cm.model_id

CodePudding user response：

Use a CASE expression:

SELECT CASE MODEL_ID
         WHEN 1 THEN 'Volvo'
         WHEN 2 THEN 'BMW'
         WHEN 3 THEN 'Ford'
         ELSE        'I don''t know'
       END AS MODEL_NAME,
       COLOR,
       BODY_STYLE
  FROM carModel