[rt-users] advanced search - customized like with wildcard

[Joop]

Payam Poursaied wrote:
>
>  
>
> Hi all
>
> I'm looking for a way to search and find those tickets which their 
> subject starts with 'FAX'.
>
> When I use "subject matches 'fax'" in advanced search, the search 
> query is something like
>
> ....
>
> main.Subject LIKE '%FAX%'
>
> ...
>
>  
>
> But I'm looking for a way to have a query with higher performance like
>
>  
>
> ....
>
> main.Subject LIKE 'FAX%'
>
> ...
>
>  
>
> Is there any way to make this query?
>
Yes, there is. I did the same thing because it kills performance each 
and everytime someone searches for something. Only thing is you need to 
make your users aware of the fact that including % is now needed if they 
don't know the exact search term.
I think you'll find the pre/post pending of % in SearchBuilder but I'm 
not completely sure so let me check.....
Yes, its in SearchBuilder.pm the following is part of a patch that I 
applied to our installation. Its here for reference only. Don't think 
you can paste it to a file and apply it.
---
--- SearchBuilder.pm.orig    Fri Jul 29 16:49:52 2005
+++ SearchBuilder.pm    Mon Aug 22 13:40:38 2005
@@ -688,9 +688,15 @@
 
     if ( $args{'FIELD'} ) {
 
-        #If it's a like, we supply the %s around the search term
+        #If it's a like, we supply the %s around the search term only 
if its not Oracle
+        #because for Oracle we'll use where contains(content,'text')>1
         if ( $args{'OPERATOR'} =~ /LIKE/i ) {
-            $args{'VALUE'} = "%" . $args{'VALUE'} . "%";
+            if ( $RT::DatabaseType == 'Oracle') {
+                 $args{'VALUE'} = $args{'VALUE'}
+            }
+            else {
+                $args{'VALUE'} = "%" . $args{'VALUE'} . "%";
+            }
         }
         elsif ( $args{'OPERATOR'} =~ /STARTSWITH/i ) {
             $args{'VALUE'}    = $args{'VALUE'} . "%";
@@ -864,6 +870,20 @@
     }
 
Regards,

Joop

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100317/3b9a7fc8/attachment.htm>