I am new to write queries , i have a table here and wanted to write a query for that
No: Desc Name
1 apple fruit
2 apple raw fruit
3 apple ripe fruit
4 APPLE fruit
5 tomato veg
6 tomato 2 veg
so here i am looking for a case statement , whatever the description(Desc) is it should be categorised as above (Name)
how do i write a case statement for that
looking forward for response
CASE WHEN Desc IN ('Apple', 'Apple raw', 'Apple ripe') THEN 'fruit' WHEN DESC IN ('tomato') THEN 'veg' ELSE 'unknown' END AS category
1 Like
it doesnt matter when there is APPLE ( in caps letter) also there is a number along with tomato as tomato 2 ?
how should I query that
Typically SQL Server is installed as case insensitive, but if you need to match bases then you'll want to do this:
CASE WHEN Desc LIKE 'Apple%' OR Desc LIKE 'APPLE%' THEN 'fruit'
WHEN DESC LIKE 'tomato%' OR Desc LIKE 'TOMATO%' THEN 'veg'
ELSE 'unknown' END AS category
1 Like
am actually trying to use this query in teradata server , will it work fine ?
In theory, yes. CASE is ANSI-standard SQL.
hi
another 2 ways of doing it
create data script
drop table if exists #Data
create table #Data(No int , Descr varchar(10) )
insert into #Data select 1 ,'apple'
insert into #Data select 2 ,'apple raw'
insert into #Data select 3 ,'apple ripe'
insert into #Data select 4 ,'APPLE'
insert into #Data select 5 ,'tomato'
insert into #Data select 6 ,'tomato 2'
drop table if exists #LookUp
create table #LooKup( Descr varchar(10) , category varchar(10))
insert into #LooKup select 'apple','fruit'
insert into #LooKup select 'tomato','veg'
1st way ..shorter code ..case
select
*
, case
when Descr like 'apple%' then 'fruit'
when Descr like 'tomato%' then 'veg'
end as Category
from
#Data
2nd way .. using a lookup table
select
*
from
#Data a
, #LooKup b
where
a.Descr like '%'+b.Descr+'%'
1 Like