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;
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;
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
