How to use multiple LIKE statements in a query with IN clause and a subquery?

Question

If I want to select some objects from dba_source which uses some function. I can write it like

Select * from dba_source where text like '%some_name%';

If I want to do the same thing for multiple names. How do i do it?

I'm aware of the usage of LIKE and OR, but the Names am getting are again from another table. So, Is there a way to do something like:

SELECT * FROM DBA_SOURCE WHERE TEXT LIKE IN(SELECT PROCESS_NAME FROM PROCESSES);

But I also want to add Wild card characters like % at the ends.

Is it possible. or can you suggest any other way?

I also tried

WITH pnames AS (SELECT PROCESS_NAME FROM PROCESSES)
SELECT * FROM DBA_SOURCE dbas WHERE INSTR(dbas.text,pnames.process_name,1,1)>0;

It didn't work.

Joining with % || % is a good idea. But, It takes more time to run. Is there any better way to deal with this

You can split the query into two (nested), where the inner query gets the list of possible words as `select '%' || word || '%' from....`. This will return a collection of wildcard'ed values that you can use within the `IN`. — FDavidov, Aug 02 '16 at 09:31
There's 3 other options in there @Gilad, including in the top answer. — Ben, Aug 02 '16 at 09:46
@Ben "Oracle query using LIKE" is not a great way to ask that question. is it? how will people identify it when the question is not very explanatory. — Arjun Raja, Aug 02 '16 at 11:34
I agree @Arjun, I picked that one because it asks the same question. You can [suggest an edit](http://stackoverflow.com/posts/6074201/edit) to the question in the future. I've edited this particular question to make it a lot clearer. — Ben, Aug 02 '16 at 12:06

score 0 · Answer 1 · answered Aug 02 '16 at 09:31

0

Use a join between the tables and let the condition by the like

SELECT D.* 
FROM DBA_SOURCE D
JOIN PROCESSES P
ON D.TEXT LIKE PROCESS_NAME || '%'

answered Aug 02 '16 at 09:31

Gilad Green

It worked. But it takes more time to run. Any other better way? – Arjun Raja Aug 02 '16 at 11:37
does make sense that it'll take more time to run. These search operations aren't cheap. You can try one of the other options in the duplicated question on look at full search - might be taking nonsense with this one - just from stuff I saw on SO – Gilad Green Aug 02 '16 at 11:45
@ArjunRaja - If any of these answers answered your specific question please consider marking question as solved :) – Gilad Green Aug 02 '16 at 14:38

score 0 · Accepted Answer · answered Aug 02 '16 at 09:33

0

SELECT *
FROM DBA_SOURCE d
  JOIN PROCESSES p ON d.TEXT like '%' || p.PROCESS_NAME || '%'

|| is ANSI SQL concatenation. I assume at least newer Oracle versions support it. Otherwise you may need to try CONCAT() function instead.

answered Aug 02 '16 at 09:33

jarlh

It worked. But it takes more time to run. Any other better way? – Arjun Raja Aug 02 '16 at 11:37
Perhaps you could do ` ... like p.PROCESS_NAME || '%'`instead. Can be optimized! – jarlh Aug 02 '16 at 11:41

2 Answers2