Can I use WHERE IN with LIKE?

Question

If I need 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 need to view 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 does not just execute WHERE IN, but also does something like WHERE LIKE? A query that simply does not look up a set of values, but searches using wildcards using a set of values.

If this is not clear, I can give an example. Let me know. Thank.

Example -

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)

It gives me -

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

But what if I need to -

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

I think 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%'

. , .

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

Answer 2

. AND:

SELECT * FROM TABLE WHERE COL1 IN (Select col from AnotherTable) AND COL1 LIKE '%test_string%'

, , JOIN:

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

Answer 3

, LIKE , IN

Answer 4

:

• IN, ,
• LIKE, ,
• REGEXP/RLIKE ( , , , " ", (ab)+a|(ba)+b aba... ba bab... ab),
• FIND_IN_SET, ( , ),
• SOUNDS LIKE, ,
• MATCH ... AGAINST .

, .

Table.Col1 LIKE CONCAT(AnotherTable.Col, '%'), , , ( , ).

Answer 5

, :

SELECT DISTINCT t.Col, t.Col1
FROM AnotherTable at
CROSS JOIN Table t 
WHERE t.col1 LIKE ('%' + at.col + '%')

, at.col. .

Answer 6

If I understand the question correctly, you want the rows from "Table" when "Table.Col1" is IN "AnotherTable.Col", and you also need rows when Col1 IS LIKE "% some_string%".

If you need something like:

SELECT 
    t.* 
FROM 
    [Table] t 
LEFT JOIN 
    [AnotherTable] at ON t.Col1 = at.Col 
WHERE (at.Col IS NOT NULL
    OR t.Col1 LIKE '%some_string%')

Answer 7

Something like that?

SELECT * FROM TABLE 
WHERE 
   COL1 IN (Select col from AnotherTable) 
   AND COL1 LIKE '%test_string%'

Answer 8

Are you thinking of something like EXISTS?

SELECT * FROM TABLE t WHERE EXIST (select col from AnotherTable t2, where t2.col = t.col like '% test_string%')