8 Replies Latest reply on Dec 18, 2015 4:04 AM by Extensitech

    ExecuteSQL finding single words




      I wonder if there is a possibility when using ExecuteSQL to search for single words in a field like we know it from FileMaker. When searching for 'def' I would like to be able to find records that have for example 'abc def', 'abc-def', 'abc/def' in a field (without having to write long queries that contain things like  ...LIKE lower('%-def%')...  


      Thanks a lot


        • 1. Re: ExecuteSQL finding single words
          Markus Schneider

          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

          • 2. Re: ExecuteSQL finding single words

            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

            • 3. Re: ExecuteSQL finding single words

              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

              * params:

              * 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 '%'" ; ""


              ] ;



              best regards


              • 4. Re: ExecuteSQL finding single words

                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.


                • 5. Re: ExecuteSQL finding single words

                  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.

                  • 6. Re: ExecuteSQL finding single words



                    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 .


                    Thanks again!


                    • 7. Re: ExecuteSQL finding single words

                      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;1)

                      ; left(field; 2)

                      ; left(field; 3)

                      ; left(field; 4)

                      ; left(field; 5)

                      ; field



                      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'.



                      • 8. Re: ExecuteSQL finding single words

                        let ( [

                        fld = "Mouse" // field in the portal

                        ; flt = "mou" // filter field

                        ] ;

                        left ( fld ; length ( flt ) ) = flt



                        Chris Cain