11 Replies Latest reply on Jul 23, 2013 11:36 AM by greglane

    Filter in SQL

    kaostika

      Is there a way nativly to filter out caharacters in SQL. If i want to look up a Tel Number ands some users put in 212-555-1212 and some put in (212) 555-1212, how do is use the SQL version of GetasNumber( tel )

       

      Oreste

        • 1. Re: Filter in SQL
          wimdecorte

          why not use FM's capabilities on the result of the SQL query?

          • 2. Re: Filter in SQL
            kaostika

            I want to generate a list of ID's not the Tel# themselves.  So I cant act on the data.

            • 3. Re: Filter in SQL
              wimdecorte

              Not sure I understand that.  Can you expand on that?

              • 4. Re: Filter in SQL
                greglane

                This won't perform well on a large record set, but assuming you have exactly 10 digits in your search criteria, you could do something like this:

                 

                Let([

                  v = Filter("(212) 555-1212"; "0123456789");

                  v1 = "%" & Left(v; 3) & "%";

                  v2 = "%" & Middle(v; 4; 3) & "%";

                  v3 = "%" & Right(v;4) & "%"

                ];

                 

                ExecuteSQL("SELECT id FROM contacts WHERE phone LIKE ? AND phone LIKE ? AND phone LIKE ?";"";""; v1; v2; v3)

                 

                )

                 

                This would also find values like 555-212-1212, which might be a problem in some situations.

                • 5. Re: Filter in SQL
                  beverly

                  OR...

                  create a field with the Filter() FileMaker function and use SQL to query it.

                   

                  Beverly

                  • 6. Re: Filter in SQL
                    taylorsharpe

                    I would make the phone an Auto Calculate field that formats phones #'s consistently say 123-456-1234 or (123)456-1234 or 123.123.1234.  Whatever format you use, create a script that formats the number field that way and set a script trigger in the field in find mode so that when you exit the field or perform the find, it will reformat the numbers properly before doing the find. 

                    • 7. Re: Filter in SQL
                      kaostika

                      I am trying to move away from native FM stuff to more SQL type Finds.  I find they are more flexiable since I can substitute text etc. 

                      • 8. Re: Filter in SQL
                        kaostika

                        So there is no FILTER command in SQL.

                        • 9. Re: Filter in SQL
                          wimdecorte

                          I think you are confusing SQL with a scripting language.  SQL is a language to communicate with a database backend to retrieve or create or update data.  Any pre-processing or post-processing you need done to get the data in the state you want would need to be done in another "language".   In FM that's the functions and script steps that you have.  In PHP it would be the functions and methods that PHP has, or .NET, or Ruby...

                           

                          Sounds like you are expecting one SQL query to replace a complete FM script and that's not a realistic expectation.

                          • 10. Re: Filter in SQL
                            beverly

                            what Wim said! You are working IN FileMaker, so use all the tools it has to offer (including the ExecuteSQL function). You will be more efficient. You can create custom functions (with Developer/Advanced FMP) that will work by passing parameters like the built-in functions. Perhaps that would allow you to be more "flexible"?

                             

                            Beverly

                            • 11. Re: Filter in SQL
                              greglane

                              The short answer is, "no". There's not an equivalent to FileMaker's Filter function in FileMaker's implementation of SQL.

                               

                              To add to the advice that others have given, using most SQL functions in a WHERE clause is a bad idea for performance reasons. FileMaker has to do a LOT of processing to evaluate those expressions. In most cases, you're better off taking a one-time hit to clean and normalize the data so it can be searched more efficiently later.

                              kaostika wrote:

                               

                              So there is no FILTER command in SQL.