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:

  1. Bach Joan Mike - get #1, #3,#4 order
  2. 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:

  1. first set: match all those n parts (both Bach and Joan and Bach match, in any order)
  2. second set: match n-1 parts (at least N-1 of my search parts match the row, in any order)
  3. 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

1 Answer 1


You don't need loops, but one CASE per searched value:

select ...
   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 > 0
order by matches desc
  • That is a great one! Brilliant! Thank you
    – Gigg
    May 9, 2016 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.