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

Question

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.

Answer 1

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.

Answer 2

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 Column 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.

Answer 3

In MySQL, use REGEXP:

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

Same in Oracle:

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

Answer 4

You could do the Like part in a subquery perhaps?

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

Answer 5

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

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

Answer 6

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.

Answer 7

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?

Answer 8

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

Answer 9

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.