SQLTeam.com | Weblogs | Forums

TSQL where condition using case


#1

Hi, I have a query that I would like to filter using itemname but could not get all the exact data based on the information, below is a sample DDL. I need to filter if idnum is P and the itemname is like 'A Frame / Main Lens / LCD' or Main LCD'.

Thank you in advance

[code]
DECLARE @Sample TABLE
( model nvarchar(35),
itemname nvarchar(100),
idnum nvarchar(20)
)

-- Populate sample data
INSERT @Sample VALUES ('B500-4000-IPH616GRY','A Frame / Main Lens / LCD Assembly - Black','P160924058')
INSERT @Sample VALUES ('B500-4000-IPH616GRY','Cover','X160924058')
INSERT @Sample VALUES ('B500-4000-IPH6S64GLD-U','A Frame / Main Lens / LCD Assembly - Black','P160924059')
INSERT @Sample VALUES ('B500-4000-IPH61GRY','Logo','C160924059')
INSERT @Sample VALUES ('B500-4000-IPH616GRY','Main LCD','P160924058')
INSERT @Sample VALUES ('B500-4000-IPH616GRY','Home','S160924058')
INSERT @Sample VALUES ('B500-0420-iPH516BLK-U','Main LCD','P160924060')
INSERT @Sample VALUES ('B500-0420-iPH516BLK-U','Main','P160924061')

select * from @sample
where
itemname like case when model like '%IPH6S%' then '%' +'A Frame / Main Lens / LCD' +'%'
when model like '%IPH6%' then '%' +'A Frame / Main Lens / LCD' +'%'
else '%Main LCD%' end[/code]


#2

where idnum like 'P%' and (itemname like 'A Frame / Main Lens / LCD%' or itemname='Main LCD')


#3

Thank you.