Using Like comparison over multiple patterns

Question

I would like to be able to match a single field based upon multiple patterns contained in another table, kind of like and IN clause for LIKE comparisons. The following query obviously doesn't work, but it expresses what I am trying to accomplish.

SELECT * 
FROM TableA 
WHERE TableA.RegCode Like (
    SELECT '%' + TableB.PartialRegCode + '%'
    FROM TableB)

I'd rather not resort to cursors if there is a better way.

Answer 1

SELECT * 
FROM TableA a
INNER JOIN TableB b on a.RegCode like '%' + b.PartialRegCode + '%'

Answer 2

If you're looking to return rows that match ANY of the patterns, this should work:

SELECT *
FROM TableA
     JOIN TableB
     ON TableA.RegCode LIKE '%' + TableB.PartialRegCode + '%'

If you're looking to return rows that match ALL of the patterns, this should work:

SELECT *
FROM TableA
WHERE ID in
(
    SELECT TableA.ID
    FROM TableA
         JOIN TableB
         ON TableA.RegCode LIKE '%' + TableB.PartialRegCode + '%'
    WHERE COUNT(*) = (SELECT COUNT(*) FROM TableB)
    GROUP BY TableA.ID
)