Search code examples
sqlregexoracle-databasesql-like

Combine LIKE and IN on keywords returned from a table

I have a table X with "Description" field. Then, I have another table Y with "Keywords". I want to do something like this:

SELECT * FROM TableX
WHERE Description
LIKE IN ('%'||(select keywords from TableY)||'%').

Of course, LIKE IN is not valid. If I remove the IN then I get an error that subquery returned more than one field. Should I be using regexp_like for this? If yes, how would the query look like.

Solution

  • You could do a join like this, but this is likely to be a very slow query in that you will not be able to leverage the use of indexes.

    SELECT DISTINCT TableX.*
FROM TableX
INNER JOIN TableY
  ON TableX.Description LIKE (CONCAT('%', TableY.keywords, '%'))

    You might consider re-visiting your schema if this is a query you expect your application to run regularly.