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

    ExecuteSQL() exact word match

    mbraendle

      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
          nickchapin

          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
            steve_ssh

            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,

             

              -steve

             

             

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

            • 3. Re: ExecuteSQL() exact word match
              mbraendle

              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.