WITH MyValues AS (SELECT v FROM (VALUES('WAR') ,('WARTEC') ,('TRIAGE') ,('LAB') ,('BDIAG')) x(v) )
SELECT m.v, s.Name, s.ref
FROM #SampleData s
JOIN MyValues m ON s.ref LIKE '%' + m.v + '%'

John