Is it possible to use WHERE IN along with LIKE?

Question

If I have to search for some data I can use wildcards and use a simple query -

SELECT * FROM TABLE WHERE COL1 LIKE '%test_string%'

And, if I have to look through many values I can use -

SELECT * FROM TABLE WHERE COL1 IN (Select col from AnotherTable)

But, is it possible to use both together. That is, the query doesn't just perform a WHERE IN but also perform something similar to WHERE LIKE? A query that just doesn't look through a set of values but search using wildcards through a set of values.

If this isn't clear I can give an example. Let me know. Thanks.

Example -

lets consider -

AnotherTable -

  id  | Col
------|------
  1   |  one
  2   |  two
  3   |  three

Table -

Col   | Col1
------|------
 aa   |  one
 bb   |  two
 cc   |  three
 dd   |  four
 ee   |  one_two
 bb   |  three_two

Now, if I can use

SELECT * FROM TABLE WHERE COL1 IN (Select col from AnotherTable)

This gives me -

Col   | Col1
------|------
 aa   |  one
 bb   |  two
 cc   |  three

But what if I need -

Col   | Col1
------|------
 aa   |  one
 bb   |  two
 cc   |  three
 ee   |  one_two
 bb   |  three_two

I guess this should help you understand what I mean by using WHERE IN and LIKE together

Answer 1

SELECT * 
FROM TABLE A
   INNER JOIN AnotherTable B on
     A.COL1 = B.col
WHERE COL1 LIKE '%test_string%'

Based on the example code provided, give this a try. The final select statement presents the data as you have requested.

create table #AnotherTable
(
    ID int IDENTITY(1,1) not null primary key,
    Col varchar(100)
);

INSERT INTO #AnotherTable(col) values('one')
INSERT INTO #AnotherTable(col) values('two')
INSERT INTO #AnotherTable(col) values('three')

create table #Table
(
    Col varchar(100),
    Col1 varchar(100)
);

INSERT INTO #Table(Col,Col1) values('aa','one')
INSERT INTO #Table(Col,Col1) values('bb','two')
INSERT INTO #Table(Col,Col1) values('cc','three')
INSERT INTO #Table(Col,Col1) values('dd','four')
INSERT INTO #Table(Col,Col1) values('ee','one_two')
INSERT INTO #Table(Col,Col1) values('ff','three_two')

SELECT * FROM #AnotherTable
SELECT * FROM #Table

SELECT * FROM #Table WHERE COL1 IN(Select col from #AnotherTable)


SELECT distinct A.*
FROM #Table A
    INNER JOIN  #AnotherTable B on
        A.col1 LIKE '%'+B.Col+'%'

DROP TABLE #Table
DROP TABLE #AnotherTable