Provide the values for a ‘like’ function from a specific column in a Table?

I am using SQL Server 2014 and I need a T-SQL query which uses the like function to run on a specific column (c1) of a Table (t1) to find out if it contains one of the codes from a list of codes found in the column (c2) of another Table (t2).

To simplify, here is the scenario and the expected output:

Table t1:

ID       Notes
101      (free text in this column)
102      ...
...      ...
115000   ...

Table t2 (list of more than 300 codes):

Code
FR110419
GB150619
...
DE111219

What I am looking for:

SELECT ID 
FROM t1
WHERE t1.Notes like (SELECT Code FROM t2)

Since the like operator needs ‘%’to work, I am confused as to how to construct that line.

I have done some research on StackOverflow and the closest solution I have come across is for a mysql problem: how to use LIKE with column name

Any type of help will be most appreciated.

Answer

You seem to be looking for a JOIN:

SELECT ID 
FROM t1
INNER JOIN t2 ON t1.Notes LIKE '%' + t2.Code + '%'

If different Codes might appear in the same Note, using an EXISTS condition with a correlated subquery is also an option, as it would avoid duplicating records in the output:

SELECT ID
FROM t1
WHERE EXISTS (
    SELECT 1 FROM t2 WHERE t1.Notes LIKE '%' + t2.Code + '%'
)

Advertisement

Answer