Re: multiple word search (forms block based on a procedure)

From: Ken Denny <ken_at_kendenny.com>
Date: Wed, 13 Mar 2002 23:32:08 GMT
Message-ID: <Xns91D0BD989F0E2kendenny_at_65.82.44.7>

[Quoted] sldcrew2k_at_yahoo.com (steve deno) wrote in news:8e6b14d8.0203131440.7812824f_at_posting.google.com:

> I have a forms block based on a pl/sql procedure that returns a pl/sql
> table. It takes a search term as an argument(a text item that is
> passed as an in parameter) Everything was splendid until the users
> wanted to be able to put multiple words in the text item and have it
> work the same way. I attacked the problem this way: The parameteer
> is called p_query_text and the field I was searching in was called
> description. So I search for spaces and try to split words out by
> adding the following to my query. I am getting unpredictable results.
> Do you know a better(preferably simpler) way?
>
> AND INSTR(p_query_text, ' ') > 0 and
> (lower(u.description)like
> '%'||lower(substr(p_query_text,1,INSTR(p_query_text,' ',1)))||'%' AND
> lower(u.description) like
> '%'||lower(substr(p_query_text,INSTR(p_query_text,'
> ',1)+1,INSTR(p_query_text,' ',2)))||'%')

One problem I see here is that if there is only one space in p_query_text then INSTR(p_query_text,' ',2) is going to return 0 making the substr null and you'll be looking for
lower(u.description) like '%' || NULL || '%' which is every row in the table. I assume you want to be able to enter one or more words in the text item, so you'll have to use dynamic SQL and build your "WHERE" clause based on the number of words in the text item. I'll give you some code I wrote to help you out. I wrote a function called "scan_to" in a former life. It works pretty much the same as the C function "strtok" which is useful for parsing character strings. Here's the code for scan_to:

FUNCTION scan_to(scanstr IN OUT VARCHAR2,

                 scanfor IN VARCHAR2 := ' ',
                 mults  IN BOOLEAN := TRUE,
                 strip  IN BOOLEAN := TRUE)

   RETURN VARCHAR2
IS
/*

|| This function scans a string for another string.  It returns the
|| portion of the string which occurs prior to the scanfor string. If the
|| strip boolean is true it strips the returned string from the original
|| string as well as the scanfor string.  If the mults boolean is true,
|| multiple occurances of the scanfor string are stripped. The mults
|| boolean has no meaning when the strip boolean is false.

*/

   startpos NUMBER;
   scount NUMBER;
   returnstr VARCHAR2(32767);
BEGIN
   scount := LENGTH(scanfor);
   startpos := INSTR(scanstr,scanfor);
   IF startpos = 0
   THEN
      returnstr := scanstr;
   ELSE
      returnstr := SUBSTR(scanstr,1,startpos-1);    END IF;
   IF strip
   THEN

      scanstr := SUBSTR(scanstr,LENGTH(returnstr)+scount+1);
      IF mults
      THEN
         WHILE SUBSTR(scanstr,1,scount)=scanfor
         LOOP
            scanstr := SUBSTR(scanstr,scount+1);
         END LOOP;
      END IF;

   END IF;
   RETURN returnstr;
END scan_to;

Now to use this to build your "WHERE" clause:

   where_clause := 'WHERE [everything up to the 'AND INST...' part]'; BEGIN

.
.
.

   WHILE p_query_text IS NOT NULL
   LOOP

      where_clause := where_clause ||
            ' AND LOWER(u.description LIKE ''%''|| ' ||
            scan_to(p_query_text,' ',TRUE,TRUE) || '||''%'')'

   END LOOP;

.
.
.

Now || where_clause on to the end of your select command and use DBMS_SQL to execute it.

-- 
Ken Denny
http://www.kendenny.com/

Planting vegetables is a great money-saver. Over the course of a summer, 
you could shave $75 off your grocery bill with just a few hundred hours of 
work

Received on Thu Mar 14 2002 - 00:32:08 CET