multiple word search (forms block based on a procedure)
From: steve deno <sldcrew2k_at_yahoo.com>
Date: 13 Mar 2002 14:40:05 -0800
Message-ID: <8e6b14d8.0203131440.7812824f_at_posting.google.com>
[Quoted] 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?
Date: 13 Mar 2002 14:40:05 -0800
Message-ID: <8e6b14d8.0203131440.7812824f_at_posting.google.com>
[Quoted] 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)))||'%')
Thanks in advance Received on Wed Mar 13 2002 - 23:40:05 CET