Use a Select Statement within a wildcard where clause

Question

Working in MS SQL 2005 and I want to use a select statement within a wildcard where clause like so:

SELECT text
FROM table_1
WHERE ID LIKE '%SELECT ID FROM table_2%'

I'm looking for product ids within a large body of text that is held in a DB. The SELECT statement in the wildcard clause will return 50+ rows. The statement above is obviously not the way to go. Any suggestions?

Answer 1

You can do a join and construct the like string based on table_2.

SELECT * FROM table_1 t1
INNER JOIN table_2 t2 ON t1.ID LIKE '%' + CONVERT(VARCHAR, t2.ID) + '%'