How to make a query on multiple columns without writting all possible permutations? I'm at my first question on stackexchange, as i have a few days since I struggle on this matter:

I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like (names: which are split- one part per column) 


Andrew Joan Bach Mike 
Mark Andrew Livy
Joan Arch Donnie
Joan Andrew Lyx


Number of parts differ from 1 to 5.

I want to search in different combinations like this:


Bach Joan Mike - get #1, #3,#4 order
Andrew Bach - get the following order #1,#2,#4 order


I don't like the idea of using a looooong query in which I will write all possible permutations in order to mach each part of my search string

What I'd like to achieve is this:


first set: match all those n parts (both Bach and Joan and Bach match, in any order)
second set: match n-1 parts (at least N-1 of my search parts match the row, in any order)
third set: match n-2 parts


I use a ORACLE database and I was thinking on making it in a stored procedure: match_my_set(query_str,col1,col2,col3,col4,col5). I would write at least 5 loops (loop into loop) in order to achieve this, but I doubt it is a professional idea . . 

Any help is appreciated. Thank you
You don't need loops, but one CASE per searched value:

select ...
from
 ( 
   select ...
      case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches
   from tab
 ) dt
where matches &gt; 0
order by matches desc