CODEWITHSUNDEEP
sqlsql-serversql-like
pavanred
pavanred

Reputation: 13843

Is it possible to use WHERE IN along with LIKE?

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

Upvotes: 3

Views: 559

Answers (8)

user521565
user521565

Reputation: 61

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%')

Upvotes: 1

John Sansom
John Sansom

Reputation: 41899

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

Upvotes: 4

Douglas
Douglas

Reputation: 37781

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.

Upvotes: 1

outis
outis

Reputation: 77450

The pattern matching operators are:

  1. IN, against a list of values,
  2. LIKE, against a pattern,
  3. 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),
  4. FIND_IN_SET to get the index of a string in a set (which is represented as a comma separated string),
  5. SOUNDS LIKE to compare strings based on how they're pronounced and
  6. 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).

Upvotes: 1

Tomas
Tomas

Reputation: 3643

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%' )

Upvotes: 0

Cătălin Pitiș
Cătălin Pitiș

Reputation: 14327

Something like this?

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

Upvotes: 0

Leslie
Leslie

Reputation: 3644

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

Upvotes: 1

Jeff Knecht
Jeff Knecht

Reputation: 2548

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'

Upvotes: 1

Related Questions