• John Mitchell-245523 - Tuesday, February 6, 2018 8:04 AM

    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

    Don't forget that this raises the possibility that a given value might match more than one of the rows in the CTE, and thus you might see multiple rows of output for only one row in the source table.   If you can avoid including m.v in the select list, you can use SELECT DISTINCT to solve that problem.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)