CODEWITHSUNDEEP
sql
Ziltoid
Ziltoid

Reputation: 482

Is there any way to combine IN with LIKE in an SQL statement?

I am trying to find a way, if possible, to use IN and LIKE together. What I want to accomplish is putting a subquery that pulls up a list of data into an IN statement. The problem is the list of data contains wildcards. Is there any way to do this?

Just something I was curious on.

Example of data in the 2 tables

Parent table

ID    Office_Code   Employee_Name
1     GG234         Tom
2     GG654         Bill
3     PQ123         Chris

Second table

ID    Code_Wildcard
1     GG%
2     PQ%

Clarifying note (via third-party)

Since I'm seeing several responses which don't seems to address what Ziltoid asks, I thought I try clarifying what I think he means.

In SQL, "WHERE col IN (1,2,3)" is roughly the equivalent of "WHERE col = 1 OR col = 2 OR col = 3".

He's looking for something which I'll pseudo-code as 

 WHERE col IN_LIKE ('A%', 'TH%E', '%C')

which would be roughly the equivalent of 

 WHERE col LIKE 'A%' OR col LIKE 'TH%E' OR col LIKE '%C'

The Regex answers seem to come closest; the rest seem way off the mark.

Upvotes: 7

Views: 24024

Answers (9)

kemiller2002
kemiller2002

Reputation: 115548

Do you mean somethign like:

select * FROM table where column IN (
   SELECT column from table where column like '%%'
)

Really this should be written like:

SELECT * FROM table where column like '%%'

Using a sub select query is really beneficial when you have to pull records based on a set of logic that you won't want in the main query.

something like:

SELECT * FROM TableA WHERE TableA_IdColumn IN 
(
     SELECT TableA_IdColumn FROM TableB WHERE TableA_IDColumn like '%%'
)

update to question:

You can't combine an IN statement with a like statement:

You'll have to do three different like statements to search on the various wildcards.

Upvotes: 3

Paul Morgan
Paul Morgan

Reputation: 32578

select *
  from parent
 where exists( select *
                 from second
                where office_code like trim( code_wildcard ) );

Trim code_wildcard just in case it has trailing blanks.

Upvotes: 1

Shawn Loewen
Shawn Loewen

Reputation: 209

I'm not sure which database you're using, but with Oracle you could accomplish something equivalent by aliasing your subquery in the FROM clause rather than using it in an IN clause. Using your example:

select p.*
from
  (select code_wildcard
   from second
   where id = 1) s
  join parent p
      on p.office_code like s.code_wildcard

Upvotes: 8

Mike Woodhouse
Mike Woodhouse

Reputation: 52326

If I'm reading the question correctly, we want all Parent rows that have an Office_code that matches any Code_Wildcard in the "Second" table.

In Oracle, at least, this query achieves that:

SELECT * 
FROM parent, second 
WHERE office_code LIKE code_wildcard;

Am I missing something?

Upvotes: 0

Tom H
Tom H

Reputation: 47402

Perhaps something like this?

SELECT DISTINCT
     my_column
FROM
     My_Table T
INNER JOIN My_List_Of_Value V ON
     T.my_column LIKE '%' + V.search_value + '%'

In this example I've used a table with the values for simplicity, but you could easily change that to a subquery. If you have a large list (like tens of thousands) then performance might be rough.

Upvotes: 2

dotjoe
dotjoe

Reputation: 26940

tsql has the contains statement for a full-text-search enabled table.

CONTAINS(Description, '"sea*" OR "bread*"')

Upvotes: 0

schooner
schooner

Reputation: 3087

You could do the Like part in a subquery perhaps?

Select * From TableA Where X in (Select A from TableB where B Like '%123%')

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425863

In MySQL, use REGEXP:

WHERE field1 REGEXP('(value1)|(value2)|(value3)')

Same in Oracle:

WHERE REGEXP_LIKE(field1, '(value1)|(value2)|(value3)')

Upvotes: 6

Mitch Wheat
Mitch Wheat

Reputation: 300827

You could use a LIKE statement to obtain a list of IDs and then use that in the IN statement.

But you can't directly combine IN and LIKE.

Upvotes: 2

Related Questions