5 Replies Latest reply on May 29, 2012 5:09 AM by reelsteve

    ExecuteSQL: How to make it case-insentitive?

    datastride

      Folks,

       

      The new ExecuteSQL function is an amazing tool, but I've run into one issue for which I could use some guidance.

       

      Take the following SELECT statement as an example:

       

      SELECT First_Name, Last_Name

      FROM Contacts

      WHERE First_Name LIKE 'morg%'

       

      I was expecting that this would find my Contact record, as my first name is "Morgan". (note capitalization)

       

      But only by changing the SELECT statement as follows does this work:

       

      SELECT First_Name, Last_Name

      FROM Contacts

      WHERE UPPER( First_Name ) LIKE UPPER( 'morg%' )

       

      I'm writing a script to process keystrokes and display matching Contact names, so I want to use the most efficient form of the SELECT statement possible. In my real script, the "morg" is actually a variable based on characters typed by the user. And I want the users to be able to type a name (or part of a name) without being concerned with capitalization. Using the UPPER function produces the deired results, but is it the best-performing solution?

       

      FileMaker's native FIND is case insensitive unless I specify "Unicode" as the language to use for indexing. I'm wondering if I can somehow force FileMaker to use the same native, case-insensitive logic for a SELECT as for the native FIND (so I get the best performance). I don't know if using the UPPER() function adds extra overhead or if it is evaluated by FileMaker in such a way as to enable use of the native (and very efficient) case-insensitive FIND logic.

       

      Thanks in advance for any light you might be able to shed on this issue ...

        • 1. Re: ExecuteSQL: How to make it case-insentitive?
          beverly

          Yes, Morgan. That is the way it works in the SQL dbs!

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: ExecuteSQL: How to make it case-insentitive?
            datastride

            Beverly,

             

            So are you saying that using the UPPER() function is the way you would do this with a real SQL database? If so, that makes perfect sense, as that's from whence I choose this appraoch.

             

            But my quetion is specific to the ExecuteSQL() function in FileMaker. Are you telling me that using UPPER() is the proper (and best-performing) method to use when searching a FileMaker database using ExecuteSQL?

             

            What I'm concerned with is the possibility that if I am searching 50,000 Contacts (for example), I wouldn't want to use some method that caused FileMaker to abandon the use of existing indexes so that each Contacts record had to be retrieved and the search field upshifted before performing the comparison to the search criteria. Does this make sense?

             

            Thanks for any clarification ...

            • 3. Re: ExecuteSQL: How to make it case-insentitive?
              beverly

              If I need case-sensitive search in SQL, it depends on the COLLATION of the column (field). If it's set to "Latin1_General_CS_AS", then it is case-sensitive. But by default it may or may not be set that way.

               

              But if I'm not sure, I might use LOWER() function (or UPPER, but I seem to prefer lower) to make a SQL search.

               

              I don't know "under-the-hood" for ExecutesSQL function in FileMaker, so I can't tell you why your search might be case-senstive even though your field is NOT set to 'Unicode' indexing. Just out of curiosity, what is it set to, Morgan?

               

              Also, I might use an "if". Search for "LIKE '%morg%'" first. If you are sure there should be records, but you return none, then do the UPPER/LOWER search next.

               

              After the holiday, let's see if the FMI folks will chime in...

               

              Beverly

              • 4. Re: ExecuteSQL: How to make it case-insentitive?
                datastride

                Beverly,

                 

                 

                 

                Thanks for your thoughts on this … I am looking for the “under-the-hood” details, so I hope the FileMaker, Inc. folks can shed some light.

                 

                 

                 

                The field in question has “English” specified for the indexing language. Thus I was surprised that the searching performed by the ExecuteSQL function was case sensitive. I always figured this new function would use the same indices and retrieval logic as a native Find, but I guess not (unless, of course, I’m just making a dumb mistake somewhere … always a possibility).

                 

                 

                 

                Peace, love & brown rice,

                 

                Morgan Jones

                 

                 

                 

                FileMaker + Web:  Design, Develop & Deploy

                 

                Certifications: FileMaker 9, 10 & 11

                 

                <http://www.onepartharmony.com/> One Part Harmony 

                 

                Austin, Texas • USA

                 

                512-422-0611

                • 5. Re: ExecuteSQL: How to make it case-insentitive?
                  reelsteve

                  Hi Morgan,

                   

                  Not an expert by any stretch but have been studying pretty heavily as of late given the magnitude of what is possible with SQL.  From my experience, there is a whole lot of case sensitivity going on.  I always use lower on both the statement as well as the argument syntax.  I can be staring at a statement that is giving me a return just as I was hoping to achieve, one in which my getting there called for the use of the double lower; lower in the statement and the argument- then to test the query's fortitude, begin pulling things away to see if a lighter query will fly but as soon as i pull either lower out...the return goes away.  From what I understand there is an "I like" which is case insensitive but filemaker doesn't support it's use.  I'm assuming u have peeped out seedcode's SQL explorer (free).  As well, skeleton key has a YouTube video dedicated to the topic as part of their filemaker academy.  It is pretty good so far.  It's about 50mins and I only got to 18.  Min 16 or so is where the I like point came from.  Check it out.

                  http://www.youtube.com/watch?v=y-j-msi3ij0&feature=youtube_gdata_player

                   

                  If u need more, I have another set of resources I can send u - email me.  Pretty sure my email addy is in

                  Y profile.  At 5a , too lazy to iPhone scroll over to my profile ;)

                  Best, Steve