@shift is a scalar variable, you are probably better of splitting the content or do some other tricks to make this work.

😎

This kind of works though

DECLARE @shift VARCHAR(100) = 'Afternoon Shift (FMF/SVP),Morning Shift ( Film18H, Fed2L),Night Shift';

DECLARE @Table TABLE

(

code VARCHAR(10)

,Description VARCHAR(100)

);

INSERT INTO @Table

( code ,Description)

VALUES

( 'CTN-A','Morning Shift ( Film18H, Fed2L)')

,( 'CTN-B','Morning Shift (Film18H)' )

,( 'CTN-C','Afternoon Shift (Film18H)' )

,( 'CTN-D','Afternoon Shift (FMF/SVP)' )

,( 'CTN-H', 'Night Shift' )

,( 'CTN-G', 'Afternoon Shift' );

SELECT

code

,SUBSTRING(s.Description,1,ISNULL(NULLIF(CHARINDEX(' (',Description,1),0),LEN(Description))) AS XShifts

,Description AS Shifts

FROM

@Table AS s

WHERE

@shift LIKE '%' + s.Description + '%';

Output

code XShifts Shifts

---------- ----------------- --------------------------------

CTN-A Morning Shift Morning Shift ( Film18H, Fed2L)

CTN-D Afternoon Shift Afternoon Shift (FMF/SVP)

CTN-H Night Shift Night Shift

CTN-G Afternoon Shift Afternoon Shift