CODEWITHSUNDEEP
mysqlsql
vellattukudy
vellattukudy

Reputation: 789

How to map a value in sql into a string value

I have a column in DB table named type. It have values like 01,78,75,98,02,11 etc. I want to map these values into a string value like, If the value starts with 01 map it into a string value named type01, and if starts with 78 the value (select result value) must be type7 etc. Any idea guys how to do it? Thanks in advance.

SQL will be somehow like below:

Select (if(type left(2) = 07 then type7), t.dept

from team t

LEFT JOIN typetable

ON typetable.id=t.team_id;

Upvotes: 3

Views: 2249

Answers (2)

Cannon
Cannon

Reputation: 168

It seems you may want a case statement if I understand correctly what you are asking.

SELECT CASE type WHEN '01' THEN 'type01' WHEN '02' THEN 'type02' WHEN '03' THEN 'type03' WHEN '04' THEN 'type04' END FROM team LEFT JOIN typetable ON typetable.id=t.team_id;

Upvotes: 3

Hogan
Hogan

Reputation: 70513

There are two ways to do this, one is with the case statement and the other is with an inline table. I like the inline table better and it is faster. You join it like any other table and use UNION ALL to construct the table. Like this:

SELECT t.dept, coalesce(type_translate.name, 'no name') as name
FROM team t
LEFT JOIN typetable ON typetable.id = t.team_id
LEFT JOIN (
   SELECT '01' as lookup, 'type01' as name
     UNION ALL
   SELECT '02', 'type02'
     UNION ALL
   SELECT '03', 'type03'
     UNION ALL
   SELECT '04', 'type04'
     UNION ALL
   SELECT '05', 'type05'
     UNION ALL
   SELECT '06', 'type06'
     UNION ALL
   SELECT '06', 'type07'
) type_translate ON left(typetable.type,2) = type_translate.lookup

If you are using a more standard DB (like SQL Server, DB2, Oracle, Progress, etc) you can use the VALUES table constructor instead of the UNION ALL -- same thing but nicer syntax.

SELECT t.dept, coalesce(type_translate.name, 'no name') as name
FROM team t
LEFT JOIN typetable ON typetable.id = t.team_id
LEFT JOIN (
   VALUES
   ('01', 'type01'), 
   ('02', 'type02'), 
   ('03', 'type03'), 
   ('04', 'type04'), 
   ('05', 'type05'), 
   ('06', 'type06'), 
   ('07', 'type07') 
) AS type_translate(lookup,name) ON left(typetable.type,2) = type_translate.lookup

Upvotes: 0

Related Questions