10 Replies Latest reply on Feb 6, 2014 11:52 AM by BruceHerbach

    ExecuteSQL and Wildcard (%) with LIKE operator ISSUE

    amy

      Hi Everyone,

       

      I am using the ExecuteSQL funtion in FileMaker 13 to set a variable using the LIKE operator with wildcards (%) on either side of another variable like this;

       

      ExecuteSQL ( "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; "%" & $$myVar & "%" )

       

      The problem I'm having is that it only returns records from the second letter of each word. For example, let's say I want to search for Purple Unicorn - and my records are as follows;

       

      Purple Polar Bear

      Purple Kangaroo

      Pink Panther

      Blue Mule

      White Unicorn

      Purple Unicorn

      Yellow Bunny

       

      If I "p" I get nothing, but if I type "u" - I will get

       

      Purple Polar Bear

      Purple Kangaroo

      White Unicorn

      Purple Unicorn

      Yellow Bunny

       

      but if I type "un" for unicorn I get only Yellow Bunny

       

      I can type "urple" and I will get -

       

      Purple Polar Bear

      Purple Kangaroo

      Purple Unicorn

       

      I can type "nicorn" and I will get

       

      White Unicorn

      Purple Unicorn

       

      But I get nothing if I type "urple nicorn"

       

      Further if I change the wildcard to be $$myVar & "%" or "%" & $$myVar NOTHING is returned regardless of what is typed.

       

      Does anyone know what is going on here. Unless the LIKE operator can be used as intended, I really don't see the purpose!

       

      Thanks,

      Amy

        • 1. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
          steve_ssh

          Hi Amy,

           

          Just as a reminder:  ExecuteSql is case sensitive.

           

          This would explain some of the results that you are seeing, though, unfortunately, not all of the results shown above.

           

          I would have still expected, for instance,  'p' to match on the lower case  'p'  in 'Purple'.

           

          On the other hand, case-sensitivity would explain, for instance, why 'un' does not match 'Unicorn', but does match 'Yellow Bunny'.

           

          Apologies if I'm just stating the obvious to you.

           

          Very best,

           

          -steve

          1 of 1 people found this helpful
          • 2. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
            BruceHerbach

            HI Amy,

             

            One possible issue is that SQL is case sensitive.  in my case I was looking for names in so I created my the $srch variable using the proper() function.  This capitalized the first letter for me.

             

            I just did this for a name look up and used $$myVar & "%".  This worked well for me.

             

            HTH

            Bruce

            1 of 1 people found this helpful
            • 3. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
              beverly

              Yes, case sensitive! I often request in this fashion to compensate:

               

                   SELECT myfield

                   ...

                   WHERE LOWER(myfield) LIKE ?

                   ....

                        "%" & Lower($myVar) & "%"

               

              Note that the SQL has a LOWER (and UPPER) function that can be used with the FileMaker function on your parameter term. This makes them 'equal' for searching. Yes it can take longer. The result has the actual field, so the Lower() is only applied for the match.

               

              also check for these gotchas:

              *     the LIKE '%term%' should be in single quotes. I'm not sure if your method is making this occur correctly. Probably as you are passing a term composed of concatenated wildcards and your variable, this IS getting formatted correctly by FileMaker

               

              •      This does not have the additional wildcard for "U" and the space would be literal in the match:

              "urple nicorn"

               

              You can also use the "_" wildcard for a single character:

                   LIKE '_urple _nicorn'

              This should take any single character regardless of case. Searching for "_" in the field, of course adds it's own complexity!

               

              see if these links help:

              <http://www.w3schools.com/sql/sql_wildcards.asp>

              <http://www.tutorialspoint.com/sql/sql-wildcards.htm>

              <http://technet.microsoft.com/en-us/library/ms179859.aspx> (of course, eSQL does NOT use the "[]" or "^" wildcards...)

               

              HTH,

              Beverly

              • 4. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
                amy

                Since SQL syntax is NOT case sensative, I was not aware of this at first, however I had tried both ways, without succes.  You got me thinking, however and I did locate the issue further up in my script!  I had requested that $$myvar to return lowercase, once I eliminated that the Execute SQL function returned correctly!  The odd thing is that I was unable to get the any result from using the wildcard (%) on one side or the other of $$myvar.  I ran the script in the debugger with the data viewer open and it appears that it simply was not executing....

                • 5. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
                  amy

                  Hi Bruce,

                   

                  That's a good idea!  In this case There are multiple words in the field that are both capitalized and lowercase, since there is a specific manner in using capitalization in these particular records, I wont have to worry about it.  Your approach could be beneficial next time!

                   

                  thanks!

                  • 6. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
                    amy

                    Hi Beverly,

                     

                    I tried using LOWER, however it would not execute either...In the end we are sticking with it being case sensative, since this solution involves specific records that will always use specific upper and lower case values, it's not a problem.  What I don't understand is why when I used the wildcards (%, _ ), either infront or behind the search term, the function failed to evaluate all together.

                     

                    Thanks!

                    Amy

                    • 7. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
                      gdurniak

                      Are you saying that both  %P and %p  fails ? 

                       

                      greg

                       

                      > What I don't understand is why when I used the wildcards (%, _ ), either infront or behind the search term, the function failed to evaluate all together.

                      • 8. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
                        amy

                        Yes!  Both;

                         

                        ExecuteSQL (  "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; $$myVar & "%" )

                         

                        and

                         

                        ExecuteSQL (  "SELECT myField FROM myTable WHERE myField LIKE ?" ; "" ; "" ; "%" & $$myVar )

                         

                        Fail to evaluatate

                        • 9. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
                          gdurniak

                          Correction:

                           

                          %p will only find a p with no trailing characters,  e.g. a word that ends with p

                           

                          greg

                           

                          > Are you saying that both  %P and %p  fails ?

                           

                          > Yes!  Both Fail to evaluatate

                           

                          Message edited by: gdurniak

                          • 10. Re: ExecuteSQL and Wildcard (%) with LIKE operator ISSUE
                            BruceHerbach

                            HI,

                             

                            Below is an abstracted query out of Seedcode's SQL explorer with two differences that may be of help.  This was being testing in the data viewier do I set the search criteria at the top with $ck = lower("P") & "%".  The second second difference is that I added the SQL LOWER function in the critteria line. 

                            "WHERE LOWER(" & aClientName & ") LIKE ? " ;

                             

                            Notice that the WHERE LOWER(  and ) LIKE ?  section are both in quotations so that they will be interpreted correctly and change the value of the search field to lower case.

                             

                            HTH

                            Bruce

                             

                            // Built by SQLExplorer.  Compliments of SeedCode… Cheers!

                             

                            Let ( [

                             

                            $ck = lower("P") & "%";

                            // Define Carriage Return Substitution Character

                            ReturnSub = "\n" ;

                             

                            // Enable the second line here if you want the header in your results

                             

                            header = "";

                            //header = "a.ID";

                             

                             

                            // Define Table variables

                            aCLIENTS = Quote ( GetValue ( Substitute ( GetFieldName ( CLIENTS::ID ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

                             

                            // Define Field Variables

                            aID = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( CLIENTS::ID ) ; "::" ; ¶ ) ; 2 ) ) ;

                            aClientName = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( CLIENTS::ClientName ) ; "::" ; ¶ ) ; 2 ) ) ;

                             

                            // Build SQL Query

                            q =

                            "SELECT " & aID & "

                            FROM " & aCLIENTS & "

                            WHERE LOWER(" & aClientName & ") LIKE ? " ;

                             

                            // Run SQL Query

                            result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ;   $ck ) ] ; 

                             

                            // Clean up carriage returns

                            List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )