3 Replies Latest reply on Apr 19, 2013 9:24 AM by mbraendle

    ExecuteSQL() exact word match


      I'm trying to replicate the =word match in classic FileMaker Find by an ExecuteSQL() SELECT statement.


      The only solution I have found so far for the WHERE part is


      field LIKE 'word' OR field LIKE 'word %' OR field LIKE '% word' OR field LIKE '% word %'


      Are there any simpler solutions?


      A less wordy expression would be


      field REXEXP '[[:<:]]word[[:>:]]'


      but REGEXP is not supported by FileMaker.

        • 1. Re: ExecuteSQL() exact word match

          Since it seems you know regex, you might want to look at the free 2Empower Text Toolkit plugin; it handles regex.

          • 2. Re: ExecuteSQL() exact word match

            Hello Martin,


            I was recently up against this very same issue, and will be curious to see if anyone has an elegant solution.



            I was doing a variation to what you have above:


               WHERE UPPER( ' ' || " & Quote( varFieldName ) & " || ' ' ) LIKE ?



            And then my substitution parameter in the ExecuteSql call was:


               "% " & Upper( varMatchString ) & " %"



            I discarded this as inadequate, however, once I realized that I still needed to take into account other word separator characters, e.g. punctuation marks, etc..


            To date, I haven't come up with anything that I like.  (pun not intended)


            Very best,





            p.s.  I enjoy a lot of your posts here.  Very good stuff.

            • 3. Re: ExecuteSQL() exact word match

              Thank you both Nick and Steve.


              Unfortunately, a RegEx plugin can not be applied in this case, since the query must be done with ExecuteSQL() only. What I have shown above will be only a small fragment of a larger, dynamically created SQL query that will be used to query a database structure which in some parts has a key-value-like data model. Something like a FileMaker-Find-like-to-internal-data-structure-translator, and also multilingual (which is typically Swiss).


              Well, I think the calculation I'm working on will need a few more intermediary steps.