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

added example.. Hope that helps. – pavanred Nov 26 '10 at 14:26 — pavanred, Nov 26 '10 at 14:26

John Sansom · Accepted Answer · 2010-11-26T15:13:39.300

4

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

edited Nov 26 '10 at 15:13

answered Nov 26 '10 at 14:11

John Sansom

41,005
9
72
84

1

he wants to be able to do: WHERE COL1 LIKE (SELECT COL FROM OTHER TABLE) – Leslie Nov 26 '10 at 14:11
added an example to my question. – pavanred Nov 26 '10 at 14:27
@Pavanred: Added a walk-through based on your example. – John Sansom Nov 26 '10 at 15:17
hmm Interesting.. Thanks, I will try this out. – pavanred Nov 26 '10 at 15:20
Watch put for escaping the values from B.Col. If B.Col = '%', then the like will be A.col1 LIKE '%%%', so all of the values will match. http://stackoverflow.com/questions/258757/escape-a-string-in-sql-server-so-that-it-is-safe-to-use-in-like-expression – Douglas Nov 27 '10 at 16:27

score 1 · Answer 2 · answered Nov 26 '10 at 14:10

1

Yes. Use the keyword AND:

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

But in this case, you are probably better off using JOIN syntax:

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

answered Nov 26 '10 at 14:10

Jeff Knecht

2,518
1
14
12

he wants to be able to do: WHERE COL1 LIKE (SELECT COL FROM OTHER TABLE) – Leslie Nov 26 '10 at 14:12
added an example to my question. – pavanred Nov 26 '10 at 14:28
@Pavanred: Thanks for the example. You will need to write a program (possibly a stored proc) that reads the values from AnotherTable and builds a SQL query containing several LIKE clauses (separated by the keyword OR) that can be executed against TABLE – Jeff Knecht Nov 26 '10 at 14:42

score 1 · Answer 3 · answered Nov 26 '10 at 14:11

1

no because each element in the LIKE clause needs the wildcard and there's not a way to do that with the IN clause

answered Nov 26 '10 at 14:11

Leslie

3,604
7
38
53

outis · Answer 4 · 2010-11-26T14:35:03.983

The pattern matching operators are:

IN, against a list of values,
LIKE, against a pattern,
REGEXP/RLIKE against a regular expression (which includes both wildcards and alternatives, and is thus closest to "using wildcards through a set of valuws", e.g. (ab)+a|(ba)+b will match all strings aba...ba or bab...ab),
FIND_IN_SET to get the index of a string in a set (which is represented as a comma separated string),
SOUNDS LIKE to compare strings based on how they're pronounced and
MATCH ... AGAINST for full-text matching.

That's about it for string matching, though there are other string functions.

For the example, you could try joining on Table.Col1 LIKE CONCAT(AnotherTable.Col, '%'), though performance will probably be dreadful (assuming it works).

score 1 · Answer 5 · edited May 23 '17 at 12:19

1

Try a cross join, so that you can compare every row in AnotherTable to every row in Table:

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

To make it safe, you'll need to escape wildcards in at.col. Try this answer for that.

edited May 23 '17 at 12:19

Community

1
1

answered Nov 26 '10 at 15:05

Douglas

36,802
9
76
89

score 1 · Answer 6 · edited Nov 26 '10 at 18:10

1

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

If so you want 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%')

edited Nov 26 '10 at 18:10

Will Marcouiller

23,773
22
96
162

answered Nov 26 '10 at 16:40

user521565

61
1
5

I think you mean "at.Col IS NOT NULL *AND*..." The concept of this answer is correct. I suspect SQL Server will automatically ignore nulls and not try to process them with the LIKE function. – IamIC Nov 26 '10 at 18:04
Actually I mean it as is using OR (similar to the answer by Catalin below but different syntax). I am reading the question as wanting the rows from "Table" when Table.Col1 is in AnotherTable.Col and also those rows where Table.Col1 is LIKE %some_string%. – user521565 Nov 28 '10 at 12:39

score 0 · Answer 7 · answered Nov 26 '10 at 14:12

0

Something like this?

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

answered Nov 26 '10 at 14:12

Cătălin Pitiș

14,123
2
39
62

added an example to my qeustion. – pavanred Nov 26 '10 at 14:27

score 0 · Answer 8 · answered Nov 26 '10 at 14:16

0

Are you thinking about something like EXISTS?

SELECT * FROM TABLE t WHERE EXISTS (Select col from AnotherTable t2 where t2.col = t.col like '%test_string%' )

answered Nov 26 '10 at 14:16

Tomas

3,573
2
20
25

added an example to question for more clarity – pavanred Nov 26 '10 at 14:30
Thanks for the example. I would have to second the oppinion of Jeff and Leslie. You would have to create a stored procedure for this, and the performance there would not be good. I would say that you need to reconsider this approach. – Tomas Nov 26 '10 at 15:05

Is it possible to use WHERE IN along with LIKE?

8 Answers8