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

    Filter in SQL


      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 )



        • 1. Re: Filter in SQL

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

          • 2. Re: Filter in SQL

            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

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

              • 4. Re: Filter in SQL

                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:



                  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


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



                  • 6. Re: Filter in SQL

                    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

                      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

                        So there is no FILTER command in SQL.

                        • 9. Re: Filter in SQL

                          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

                            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"?



                            • 11. Re: Filter in SQL

                              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.