CODEWITHSUNDEEP
mysqlsql
Torben
Torben

Reputation: 5494

SQL - replace value in SELECT clause

I have the following database column and it's values:

Column
#1 747, 757, 767
#2 747, 757
#3 767, 777
...

I want to query this table with a SQL SELECT and the output should be something like this:

#1 Tree, Beer, Car
#2 Tree, Beer
#3 Car, House

So I need to kind of translate the numeric codes in words. How can I achieve this in a select clause?

Thanks!

Upvotes: 0

Views: 811

Answers (2)

Esty
Esty

Reputation: 1912

It will be very bad to hardcode like this but this is the only way now in my head

Select 
COLUMN1, 
CASE COLUMN2 WHEN 747 THEN 'TREE' WHEN 757 THEN 'BEER' WHEN 767 THEN 'CAR' WHEN 777 THEN 'HOUSE' END AS COLUMN2,
CASE COLUMN3 WHEN 747 THEN 'TREE' WHEN 757 THEN 'BEER' WHEN 767 THEN 'CAR' WHEN 777 THEN 'HOUSE' END AS COLUMN3,
CASE COLUMN4 WHEN 747 THEN 'TREE' WHEN 757 THEN 'BEER' WHEN 767 THEN 'CAR' WHEN 777 THEN 'HOUSE' END AS COLUMN4
FROM YOURTABLE

Upvotes: 0

Andy K
Andy K

Reputation: 5044

You can try that 

CREATE TEMPORARY TABLE #temp (COL1 VARCHAR(50))


INSERT INTO #temp VALUES ('#1 747, 757, 767'),
INSERT INTO #temp VALUES ('#2 747, 757'),
INSERT INTO #temp VALUES ('#3 767, 777')


SELECT replace(
replace(
replace(
replace(COL1, '747', 'Tree'), 
'757', 'Beer'), 
'777', 'House'), 
'767', 'Car') required_value 
FROM #temp

Should I mention the table design can be improved by putting the values in different columns.

If you are planning to undo your current design and have another one, you will need testing but I guess it is not the aim of the question. ;)

Upvotes: 1

Related Questions