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

Question

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

Just what I was interested in.

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% 

---

Clarification Note (via third-party)

Since I see several answers that do not seem to affect what Ziltoid asks, I thought that I would try to clarify what, in my opinion, he means.

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

He is looking for something that I will be pseudo-code like

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

which would be roughly equivalent

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

Regex answers seem closest; the rest look uneasy.

Answer 1

I'm not sure which database you are using, but with Oracle you can do something equivalent by placing a subquery in the FROM clause, rather than using it in the IN section. 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 2

In MySQL use REGEXP :

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

The same thing in Oracle :

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

Answer 3

You mean somethign like:

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

Indeed, it should be written like this:

 SELECT * FROM table where column like '%%' 

Using a subselect query is really useful when you need to pull records based on a set of logic that you don't need in the main query.

something like:

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

update question:

You cannot combine an IN statement with a similar statement:

You will need to make three different types of operators to search for different wildcards.

Answer 4

You can use the LIKE operator to get a list of identifiers, and then use it in an IN statement.

But you cannot directly combine IN and LIKE.

Answer 5

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 used a table with values ​​for simplicity, but you can easily change it to a subquery. If you have a large list (for example, tens of thousands), performance can be rude.

Answer 6

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

Trim the wildcard_code only if it has trailing spaces.

Answer 7

Could you do the Like part in the subquery, perhaps?

Select * From Table A Where X's (select A from TableB, where B Like '% 123%')

Answer 8

tsql has contains for a table with full-text search support.

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

Answer 9

If I read the question correctly, we want all the lines of the parent to have the Office_code code that matches any Code_Wild card in the "Second" table.

In Oracle, at least this query reaches:

 SELECT * FROM parent, second WHERE office_code LIKE code_wildcard; 

Did I miss something?