6 Replies Latest reply on Jan 18, 2017 11:14 PM by makerbert

    underscore in LIKE not escapable

    makerbert

      Hi all,

       

      I have a list of names derived from a legacy system. Some names are like "YADDAYADDA_FK", others might have names like "THISAFK" or "THATZFK" and hold data which I'm not interested in.

       

      The last THREE characters are important, including the underscore. Unfortunately the underscore is a single character wildcard in sql thus the executeSQL below returns both "YADDAYADDA_FK", "THISAFK" and "THATZFK" but I really only want the first.

       

      Query in use is:

      ExecuteSQL (

      "

      SELECT count(column_name) FROM tabe_COLU

        WHERE table_name LIKE ?

             AND column_name LIKE ?

      "

      ; "" ; "";

      table_name ; "%_FK"

      )

       

      This query works but also returns THISAFK and THATZFK, which is expected and understood but not what I need.

       

      Various internet sources state I should escape the underscore by encasing it in square brackets:

       

       

      table_name ; "%[_]FK"

       

       

      but if i do this nothing is returned

       

       

      Anyone any idea how to escape the underscore?

        • 1. Re: underscore in LIKE not escapable
          philmodjunk

          Different flavors of SQL escape text differently see if:

           

          "%"\"_\"FK"

           

          Works.

           

          I don't see much reason for setting this up as an optional parameter given that you are using a constant value and not a value from your current record or a variable.

          • 2. Re: underscore in LIKE not escapable
            CamelCase_data

            Try using  "%\_FK" as the second search value.

            1 of 1 people found this helpful
            • 3. Re: underscore in LIKE not escapable
              beverly

              the "_" is a wildcard character (for any single character) with the SQL LIKE, so it cannot be used this way.

              I don't believe that ExecuteSQL uses the square brackets that way and I don't believe there are other methods of escaping the character for this type of search.

              it might be better if you create a calculated (stored or not) field for such searches (even with ExecutesSQL).

               

              beverly

               

              p.s. I see the other answers now. you can try the \_

              • 4. Re: underscore in LIKE not escapable
                makerbert

                philmodjunk is right when he says there is no reason to have the constant in a parameter but that's my lack of experience in executeSQL I'm afraid

                 

                But I rewrote it as a constant (single quotes in stead of double!) AND used the backslash from David's post. My calculation is now:

                 

                ExecuteSQL (

                "

                SELECT count(column_name) FROM tabe_COLU

                  WHERE table_name LIKE ?

                       AND column_name LIKE '%\_FK'

                "

                ; "" ; "";

                table_name

                )

                 

                and this works OK! The underscore gets selected like a normal character.

                 

                Thanks all for your comments and I hope to be able to return the favor.

                • 5. Re: underscore in LIKE not escapable
                  beverly

                  great! I wonder if the escaped value as a parameter is getting something converted (or not) in FM calc dialog (as that is what is evaluating the ExecuteSQL). It might be worth a try.

                  At least you know what SQL expects "_" (underscore) to be in the query (a wildcard). ExecuteSQL is not quite like the rest of the SQL's out there, so the square brackets didn't help.

                  The single quote is how the value is constant inside the query. The double quote is how you pass the literal parameter and FM is supposed to take care of providing the single quote for text values.

                  Thanks for playing! and passing along the solution!

                  beverly

                  • 6. Re: underscore in LIKE not escapable
                    makerbert

                    Actually it's me who has to thank you all in this community and other internet sources for sharing so much filemaker experience and knowhow.

                     

                    I would not be where I am now had I not be able to find al this shared knowledge in the past three years.

                     

                    Bert