I was given this little problem by a colleague and I've been mulling it over for a few days with little success.
Apologies in advance for the bad explanation... I have a table containing employee types, when I query the table I want to return multiple types. In the example below, if I query Emp_Type for 'Teacher Primary' then I'd like to return matches for :
'Teacher Primary & Secondary'
'Instructor Primary & Secondary'
I'm not a huge fan of CASE statements and in all honesty if I wrote out every eventuality it would be long and ugly.
Is there a better way of achieving this that I'm over looking...?
CREATE TABLE #TempEmployee ( Emp_ID INT PRIMARY KEY, Emp_Name VARCHAR(20), Emp_Type VARCHAR(40) ) INSERT INTO #TempEmployee VALUES (1, 'Bob', 'Teacher Primary'), (2, 'Danny', 'Teacher Secondary'), (3, 'Steve', 'Teacher Primary & Secondary'), (4, 'Ruby', 'Instructor Primary'), (5, 'Lisa', 'Instructor Secondary'), (6, 'Chris', 'Instructor Primary & Secondary'), (7, 'Robby', 'Teaching Assistant'), (8, 'Anne', 'Nursery Assistant'), (9, 'Matt', 'Nursery Nurse'), (10, 'Jenny', 'Nursery Nurse / TA') Declare @Wanted_Type Varchar(20) SET @Wanted_Type = 'Teacher Primary' SELECT Emp_Name, Emp_Type FROM #TempEmployee WHERE Emp_Type LIKE CASE WHEN @Wanted_Type = 'Teacher Primary' THEN 'Teacher%' + '%Primary%' WHEN @Wanted_Type = 'Teacher Secondary' THEN 'Teacher%' + '%Secondary%' WHEN @Wanted_Type = 'Teacher Primary & Secondary' THEN 'Teacher%' + '%ary%' WHEN @Wanted_Type = 'Nursery Nurse / TA' THEN 'Nursery Nurse' END DROP TABLE #TempEmployee