CASE statement - help needed in writing query

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.


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 
        when Descr like 'apple%'  then 'fruit' 
		when Descr like 'tomato%' then 'veg' 
	 end as Category  

2nd way .. using a lookup table

   #Data a
 , #LooKup b 
    a.Descr like '%'+b.Descr+'%'

1 Like