List view searches and %

[dseaton]

From the Sugar Open Source User and Administrator Guide Version 4.2 March 2006
I read the following...
List view searches
When working with the list view, you can work with the complete list, or use the search filter to reduce
it to a more manageable size. To use the search filter, type in one or more boxes and then click on
the Search button. Text entered in search fields needs to match from the beginning of the value
stored in each record – for example “fred” will match Frederick, but not Alfred, in a first name field. If
text is entered in more than one search field, then a record must match on all those fields to be
included in the filtered list. Wild card search characters are not needed and not supported. In other
words, searching for “fred*” would only match first names like “Fred*erick” or other “Fred” names
containing the asterisk (*) character.

It make it very hard to search for some things when the value in the record is "the tool" and you need to search for tool.
we almost dropped sugar because we couldn't search our existing data well enough.

Then we discovered that if you enter %tool it will find tool in "the tool" and if you enter %fred it will find Alfred.
Maybe I missed something somewhere or this is a well known search method but it would help if it was documented.
I hope this helps someone else learn about it.

[JohnnyB]

Man, thank you sooo much! We couldn't figure this out. Granted I spent about five minutes on it unil I came to the forums.

This was VERY helpful though. I dont know how we would've been able to use it without this.

[richardk@idealogical.com]

It's not documented anywhere as far as I know, but in actuality it's not a feature of Sugar, but of the db/MySQL as I mention in here:

http://www.sugarcrm.com/forums/showthread.php?t=15289

From what I can tell, the searches in Sugar are done as a "LIKE" -- ... WHERE foo LIKE 'value%' AND bar LIKE 'value2%' ...

The % wildcard is being tacked on in the query, so when you yourself add a leading % in a search, it turns out as ... WHERE foo LIKE '%value%' AND bar LIKE '%value2%' ...

I was happy too when I discovered that, but in my case I'm searching for a way to suppress it :S

[richardk@idealogical.com]

Just an update if you were interested -- I've tracked it down and confirmed what I figured -- it does indeed tack on %'s for LIKE's in a WHERE clause.

If it's any use to someone, In the thread I linked to in the previous message, I've updated and detailed my modifications on how to strip any user-inputted leading %'s to basically force it into Sugars originally-intended search method.

[Bart.Steanes]

I'd appreciate an explanation of how to do this, and in addition, how to remove the "implicit" trailing %.