CODEWITHSUNDEEP
t-sqlsql-server-2008
TGnat
TGnat

Reputation: 4001

Using Like comparison over multiple patterns

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.

Upvotes: 1

Views: 972

Answers (2)

Aaron
Aaron

Reputation: 7098

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
)

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

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

Upvotes: 4

Related Questions