SQL manuals I've found mostly mention to use '%' or '% ' as wildcard, etc. - means longer queries as You wrote...
would be interested too
SELECT TheName FROM Adresses WHERE TheSearch LIKE?
parameters for TheSearch:
"%" & Table::SearchString &"%"
-> will search for all that contains the string
"% " & Table::SearchString &"%"
-> will search for words only that begin with the string
How about not using ExecuteSQL as this is likely to not be as responsive as you wish doing this
Create a global field
Create a field with the calc Patterncoount ( search::field ; table:;global ) > 0
Create a field which is a Summary List of the ID field
Do a find on calc field = 1
Your List gives you the record IDs
Beside the %-wildcard for any character, underline "_" for a single character seems to work too.
For not to have to write all the complicated queries over and over again, I made a CF which takes multiple search terms as a parameter:
SetQueryConditionLike ( dField ; condition )
* ©2014 Otmar Kramis
* dField = data field name
* condition = search terms: % is added at the end, * gets replaced through %,
* multiple search terms separated by "," for an OR condition or "&" for an AND condition, range a-d finds everything starting with a through d
* example: SetQueryConditionLike ( "p.Lastname"; "me_er,ma_er,ma_r" )
* returns: (Upper(p.Lastname) like 'ME_ER%' OR Upper(p.Lastname) like 'MA_ER%' OR Upper(p.Lastname) like 'MA_R%')
Let ( [
_result = Substitute (
Upper ( condition )
; [", " ; ","]
; ["& " ; "&"]
; ["," ; "%' OR Upper(" & dField & ") like '"]
; ["&" ; "%' AND Upper(" & dField & ") like '"]
; _result = Substitute (
; "*" ; "%"
) & "%')"
; _result = Substitute (
"(Upper(" & dField & ") like '" & _result
; " OR Upper(" & dField & ") like '%'" ; ""
While my method might not be the same, I'm with JR here. Don't use ExecuteSQL() - the use of LOWER (or UPPER) and the wildcard is necessary, as case matters.
FM native finds (within fields) are terrific, if you know how they work. Added bonus: case does NOT matter and you have so many more 'wildcards'.
JR is working from the functions, which also are great. You might consider all of the text functions and rather than creating a calc with a single function make it:
Evaluate(table::global) and you can put more than PatternCount() in your global. The global would hold the function to be 'run'.
Or as I prefer, script the find rather than calculate and find.
To be fair, I was just suggesting that there are other, possibly better ways to do this that rely on built-in functionality which is designed to be as performant as possible. Typing while only half awake, never that smart....
ExecuteSQL is an interpretive layer over the native functionality, so where it can give us things that might otherwise be hard to find then use it, but where there are native ways (some of which MAY be be a little lateral) then use them first
I listened to a talk from Clay Maeckel talking about the find perfomance on mega-huge systems and how well the find mechanism is engineered for maximum speed.
Wow, Thanks so much for your input and ideas. Really a great help.
Perhaps I should've mentioned that I'm trying to filter a portal (without creating calculated fields that use m, mo, mou, mous, mouse as result). I agree with Beverly: FileMaker really offers a lot of easy-to-use find features without having to write complicated finds.
My sources for using ExecuteSQL for filtering portals are Dave Hobson's Using eSQL to filter a “QuickFind” portal and Matt Pattrowsky's Portal Filtering with ExecuteSQL . I must admit I don't clearly see the advantage of using ExecuteSQL here, I hoped to avoid the mentioned calc fields .
yes, but you ARE calculating (within the Filter). If you can create a calculation field, you can use the same logic to create the Filter, can't you?!
An "old school" method (pre ExecuteSQL function) was to have a calculation (perhaps auto-enter to index the field):
; left(field; 2)
; left(field; 3)
; left(field; 4)
; left(field; 5)
Then relate using multi-line key.
So within the Filter, do something similar:
left(qf; 1) = left(field; 1)
left(qf; 2) = left(field; 2)
qf = field
Of course it may depend on how far you want to take the "begins-with-by-calc". I used this with several words where the client needed to search on several words. But it's pretty honking long and the auto-enter with relationship may even be preferable at some point.
And there are some times when it just makes sense to open a new window, do the find and gather the keys necessary to 'filter'.
let ( [
fld = "Mouse" // field in the portal
; flt = "mou" // filter field
left ( fld ; length ( flt ) ) = flt