Re: how to search multiple columns?

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 27 Jul 2005 08:03:06 -0700
Message-ID: <1122476586.620955.255460@o13g2000cwo.googlegroups.com>

If your current query gives you want you want (appart from the column) you could do something like this:-

select score,

          project_id,
          case when instr(objectives,'search term',1,1) > 1 then


'Objectives'

                  when instr(objectives,'search term',1,1) > 1 then


'Approach'

                  when instr(scope,'search term',1,1) > 1 then 'Scope'
                  when instr(title,'search term',1,1) > 1 then 'Title'
          end

from ( your original query here, only also select the other cols too )

This may not be the best suggestion, as depending on how many rows are returned, and how much data is in each of the cols, it could be slow. If you are displaying search results, say 10 at a time, then you could limit the results of the inner select to 10 rows, and the outer select should be alright. Note that it doesn't cater for the case when the search term could appear in more than one of the cols.

Ignore my comment on Bind vars if you are using this approach - I was refering to the dynamic sql option.

Stephen. Received on Wed Jul 27 2005 - 10:03:06 CDT