Combine LIKE and IN using only WHERE clause

Question

I know this question has been asked, but I have a slightly different flavour of it. I have a use case where the only thing I have control over is the WHERE clause of the query, and I have 2 tables.

Using simple example:

Table1 contains 1 column named "FULLNAME" with hundreds of values
Table2 contains 1 column named "PATTERN" with some matching text

so, What I need to do is select all values from Table 1 which match the values in table 2.

Here's a simple example:

Table1 (FULLNAME)
ANTARCTICA
ANGOLA
AUSTRALIA
AFRICA
INDIA
INDONESIA

Table2 (PATTERN)
AN
IN

Effectively what I need is the entries in Table1 which contain the values from Table2 (result would be ANTARCTICA, ANGOLA, INDIA, INDONESIA)

In other words, what I need is something like:

Select * from Table1 where FULLNAME IN LIKE (Select '%' || Pattern || '%' from Table2)

The tricky thing here is I only have control over the where clause, I can't control the Select clause at all or add joins since I'm using a product which only allows control over the where clause. I can't use stored procedures either.

Is this possible?

I'm using Oracle as the backend DB

Thanks

Answer 1

One possible approach is to use EXISTS in combination with LIKE in the subquery:

select * from table1 t1 
where exists (select null 
              from table2 t2 
              where t1.fullname like '%' || t2.pattern || '%');

Answer 2

I believe that you can do this with a simple JOIN:

SELECT DISTINCT
    fullname
FROM
    Table1 T1
INNER JOIN Table2 T2 ON T1.fullname LIKE '%' || T2.pattern || '%'

The DISTINCT is there for those cases where you might have a match to multiple rows in Table2.

Answer 3

If the patterns are always two characters and only have to match the start of the full name, like the examples you showed, you could do:

Select * from Table1 where substr(FULLNAME, 1, 2) IN (Select Pattern from Table2)

Which prevents any index on Table1 being used, and your real case may need to be more flexible...

Or probably even less efficiently, similar to TomH's approach, but with the join inside a subquery:

Select * from Table1 where FULLNAME IN (
  Select t1.FULLNAME from Table1 t1
  Join Table2 t2 on t1.FULLNAME like '%'||t2.Pattern||'%')

Answer 4

Right, this involved a bit of trickery. Conceptually what I've done is turned the column from the PATTERN into a single cell, and use that with REGEX_LIKE

So the values "AN and IN" becomes one single value '(AN|IN)' - I just feed this to the regexp_like

SELECT FULLNAME from table1  where  
regexp_like(FULLNAME,(SELECT '(' || SUBSTR (SYS_CONNECT_BY_PATH (FULLNAME  , '|'), 2) || ')' Table2
          FROM (SELECT FULLNAME , ROW_NUMBER () OVER (ORDER BY FULLNAME) rn,
                       COUNT (*) OVER () cnt
                  FROM Table2)
         WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1))

The subquery in the regexp_like turns the column into a single cell containing the regular expression string.

I do realise this is probably a performance killer though, but thankfully I'm not that fussed about performance at this point