1 2 Previous Next 22 Replies Latest reply on Sep 21, 2014 4:05 AM by beverly

    SQL function to remove spaces between words

    jaysayers

      Is there an SQL function to remove spaces between words within a column? I have tried the SQL 'REPLACE' function but FileMaker Pro returns the following error:

       

      ExecuteSQL: Error Executing File SQL; FileMaker Error: ERROR: FQL0030/(1:113): "REPLACE" is an invalid function

       

      Example:

       

      Student NameGrade
      Jay Sayers9
      Steve Jobs4
      Salma Hayek12

       

      I want to remove the space between "Steve Jobs" to return "SteveJobs"

       

      Please let me know if I can provide any more information to help clarify the issue.

       

       

      Thanks in advance!

        • 1. Re: SQL function to remove spaces between words
          BruceRobertson

          Why not operate on the result instead? Using a substitution.

          • 2. Re: SQL function to remove spaces between words
            jaysayers

            Bruce,

             

            Thanks for the feedback.

             

            I was hoping to accomplish this in a single SQL statement.

             

            Jay Sayers

            • 3. Re: SQL function to remove spaces between words
              BruceRobertson

              Why bother?

              You would use a single SQL statement.

              It's just what you do with the result.

               

              Let( theResult = ExecuteSQL( .... ); substitute( theResult; " "; ""))

              • 4. Re: SQL function to remove spaces between words
                jaysayers

                Okay Bruce, thanks for the example and now I understand your comment of why bother.

                 

                In reality, I would be returning several columns/fields (approx 10-15) in a somewhat complex multi-join SELECT statement and would want the replace/substitute to occur only in one column.

                 

                Otherwise your suggestion would hit it on the mark.

                 

                Jay

                • 5. Re: SQL function to remove spaces between words
                  beverly

                  There IS a SQL function <http://msdn.microsoft.com/en-us/library/ms186862.aspx> that does that:

                   

                       REPLACE(value, ' ', '')

                   

                  However, It's unlikely to be valid within ExecuteSQL. Or at least in my testing it would not work.

                   

                  Beverly

                  • 6. Re: SQL function to remove spaces between words
                    erolst

                    Jay Sayers wrote:

                    a somewhat complex multi-join SELECT statement and would want the replace/substitute to occur only in one column.

                     

                    In that case, create a “space-less” calculated version of the studentName field and include that one in the query. It ain't nice, but it works.

                    • 7. Re: SQL function to remove spaces between words
                      steve_ssh

                      Hello Jay,

                       

                      Depending on your data and the anticipated size of your found sets, you might be able to run the ExecuteSQL result of a carefully crafted query through the Evaluate function to obtain the desired result.

                       

                       

                      Caveats:

                       

                        - If there are quote chars in your data, this could easily break

                       

                         - The technique is straighforward, but it can require extra attention to detail to get it working right

                       

                       

                       

                      As a simplistic example:

                       

                      The following calc is designed to substitute out space chars in field01, leaving field02 intact.

                       

                      Let([

                       

                        varSQL = "SELECT 'Substitute( \"' || field01 || '\"; \" \"; \"\" ) & \", \" & \"' || field02 || '\"' from Test";

                       

                        varQueryResult = ExecuteSQL( varSQL; ""; " & Char( 13 ) & " )

                       

                      ];

                       

                       

                        Evaluate( varQueryResult )

                      )

                       

                       

                       

                      Important: The above code breaks if either field contains a quote char in the data.


                       

                      A small amount of discussion about this technique can be found at the following thread:  https://fmdev.filemaker.com/message/122257

                       

                      HTH,

                       

                      -steve

                       

                       

                       

                       

                      Edit:  Having just read erolst's comment, I believe that to be a much more sound suggestion.

                       

                      Edit #2:  Not only that, I think I originally got the code wrong above.  My sincere apologies.  I didn't test with a found set of multiple rows and what I posted was bad.  Just now updated it with what I believe is correct.

                      • 8. Re: SQL function to remove spaces between words
                        alquimby

                        Jay,

                         

                             You can use the FileMaker TrimAll function, in this case: TrimAll ( Name ; 1 ; 3 ).

                         

                             With existing values, you can use Replace Field Contents > Replace with Calculated Result. For new values set your field to Auto-Enter calculation replaces existing value (with the above funtion).

                         

                        Al Quimby

                        • 9. Re: SQL function to remove spaces between words
                          user19752

                          It seems REPLACE is not standard SQL function

                          http://www.postgresql.org/docs/9.3/static/functions-string.html

                          and it is not supported in FM and FM doesn't reserve the word.

                           

                          Adding calc field may be simple workaround. This is able to be unstored, may not be worse than calculating in SQL.

                          • 10. Re: SQL function to remove spaces between words
                            beverly

                            Yes, REPLACE is a standard SQL function (in some flavors of SQL). Note my reply with the link to MS SQL. Also, in my extensive testing this function does NOT work within ExecuteSQL().

                             

                            agreed that sometimes we need to make a field (calculated) for use with queries. IF this is going to be an oft-used field, my preference is to make it auto-enter and STORED if at all possible.

                             

                            steve_ssh also came up with a great example (as always)! and it seems to use the SUBSTITUTE() SQL function <http://msdn.microsoft.com/en-us/library/ee634233.aspx>. I don't believe I tested this function with ExecuteSQL(). or the "Evaluate" makes it a FileMaker function. Can anyone confirm this?

                             

                            beverly

                            • 11. Re: SQL function to remove spaces between words
                              steve_ssh

                              Hi Beverly,

                               

                              The example I posted is not using the SQL SUBSTITUTE function -- it's using the FileMaker Substitute().

                               

                              Assuming Jay just needs to format that one single field, I think that creating the additional field in the schema is a better solution.

                               

                              In other cases, where there are more complex needs for transforming the output, I think the Evaluate technique might be appropriate, but it probably should be modified from what I posted in order to allow for quotes chars in the data without breaking.

                               

                              Very best!

                               

                              -steve

                              • 12. Re: SQL function to remove spaces between words
                                beverly

                                Further testing on this. (see my other replies)

                                 

                                I used my example files and created this query:

                                 

                                Let (

                                     [ $query = "SELECT 'Substitute(\"' || name || '\";\" \";\"\")', salespersonID

                                     FROM salesperson"

                                     ; $result = ExecuteSQL ( $query; " & char(9) & "; " & char(13) & "  )

                                     ]; $result

                                ) // NOTE: still must be "evaluated", but the correct characters would appear in the final text, including tabs and returns if desired

                                 

                                Keeping in mind that the text must be evaluated to get the text as needed. It's using FMP Substitute() function, not SQL's SUBSTITUTE(), which I did not test. But since it's using the calc engine to perform the substitute, the use of another field to give that, trimmedName=Substitute(name;" ";""), might just as well be used in the query:

                                 

                                Let (

                                     [ $query = "SELECT trimmedName, salespersonID

                                     FROM salesperson"

                                     ; $result = ExecuteSQL ( $query; Char(9); Char(13)  )

                                     ]; $result

                                ) // NOTE: does NOT need to be evaluated

                                 

                                But I do like the thinking outside the box with the concatenation in the eSQL.

                                 

                                Beverly

                                • 13. Re: SQL function to remove spaces between words
                                  beverly

                                  LOL! Steve, I was testing and just replied my findings. I confirmed that you meant FMP's Substitute().   See my example for ways to put the tabs and returns into the text that must be 'evaluated'.

                                   

                                  beverly

                                  • 14. Re: SQL function to remove spaces between words
                                    erolst

                                    I think one could also “go back to the roots source” and do something like …

                                     

                                    ExecuteSQL ( " SELECT nameFirst + nameLast, grade etc )

                                     

                                    Jay Sayers wrote:

                                    I want to remove the space between "Steve Jobs" to return "SteveJobs"

                                    Why?

                                    1 2 Previous Next