14 Replies Latest reply on Dec 28, 2016 4:50 AM by CICT

    SQL Extract Number from a String


      We've an 84 column (don't you just love insurance company data extracts?"  SQL SELECT that we need to add a new entry  that needs a numeric value extracted from a string of text, such as '1000 per annum' we need '1000'.


      I can do this in FileMaker either by simply GetAsNumber or using Position. However, this requirement has to be embedded within the SQL and I can't see a way around not having (according to FMP15 SQL Reference) CHARINDEX, PATINDEX or INSTR to use to find the first space within the string to use LEFT or NUMVAL or similar.


      The only way I can see at the moment, as this particular example conveniently commences with the number, is to use a predefined number of entries in CASE searching for the first space using CHR. However, this is finite and as our currency includes Dirham, the figures can get pretty big and it wouldn't be robust coding.


      I'm considering either cheating in FileMaker and taking the number extract into another field or even generating the report and then subsequently populating this particular column using standard FileMaker functions. But there are subtables involved and there is some degree of risk. These would be a last resort though.


      Any constructive help much appriciated.


      Kind regards



      (p.s. Merry Christmas to all FM Developers and here's to a happy, healthy and prosperous New Year)

        • 1. Re: SQL Extract Number from a String

          Depending on the dataset size, you could send the result through a CF like this:


          columnStringToNumber ( _list ; _column ; _delimiter )



          columnStringToNumber ( _valuelist; _column; ; _delimiter )


          by Otmar Kramis, Hochschule Luzern



          _list: list returned by the List ( ) - function or EcecuteSQL ( ) with default row delimiter

          _column: the column to be processed

          _delimiter: column delimiter of the list



          Let ( [


          n = ValueCount ( _list ) ;

          row = Substitute ( GetValue ( _list ; 1 ); _delimiter ; "¶" ) ;

          nrow = ValueCount ( row ) ;

          value = GetValue ( row ; _column ) ;

          newvalue = If ( value = "?" ; 0 ; GetAsNumber ( value ) ) ;

          newrow = LeftValues ( row ; _column - 1 ) & newvalue & ¶ &  RightValues ( row ; nrow - _column ) ;

          newrow = Left ( newrow ; Length ( newrow ) - 1 )


          ] ;


          Substitute ( newrow ; ["¶" ; _delimiter] )


          Case ( n > 1 ; ¶ & columnStringToNumber ( RightValues ( _list ; n - 1 ) ; _column ; _delimiter ) )




          best regards


          • 2. Re: SQL Extract Number from a String

            Many thanks Otmar


            I can't do this within FileMaker functions, it has to be within the SELECT statement within the ExecuteSQL, which appears to be light on the supported functions we need.


            I have got around this by creating an auto calculation field in FileMaker and extracted the value using GetAsNumber and pointing the SELECT to this field instead, but it seems a shame to have to add a field just for this purpose.


            Kind regards



            • 3. Re: SQL Extract Number from a String

              Could you not do this in a let()-statement?


              Let ( [


              _delimiter = Char(9)

              ; _q = yourQuery

              ; _list = ExecuteSQL ( _q ; _delimiter ; "" )

              ; _n = theColimnNumbertoProcess


              ] ;


              columnStringToNumber ( _list ; _n ; _delimiter )



              • 4. Re: SQL Extract Number from a String

                Pardon me, Andy. Is the result going into a SQL db or is it the text result from a SQL column from which you are trying to 'extract' part of the value? 



                Sent from miPhone

                • 5. Re: SQL Extract Number from a String

                  Not sure why it "must" be done in a SELECT statement - especially since FMP can't do what you need, apparently, but I would argue that the real issue is that you don't appear to have a database design (a numeric field to hold 1000) that supports your reporting requirements. IOW, you're solving the wrong problem.


                  Although the database's design might be beyond your control, that would be the first thing I'd fix. In FileMaker you could then create a calculated field (I know you know this already) to hold just the 1000 or whatever number you extract from the "x per annum' field so the table is ready for your SQL.


                  You could also pre-process the table before doing the SQL if you can't update the database.


                  Even in MySQL, although I've never had to do this, I would probably just write a stored procedure (that is, if I couldn't fix the "actual" problem -> the database design).


                  HOPE THIS HELPS.

                  • 6. Re: SQL Extract Number from a String

                    I just used ROUND in a SQL statement within FMP,  and I believe we use CAST in some of our imports to avoid having to clean up the import. Have you tried CAST on this column?


                    Edit: Sorry, it appears you will get an error message with text surrounding the number.

                    1 of 1 people found this helpful
                    • 7. Re: SQL Extract Number from a String

                      Hi Beverly


                      Belated Merry Christmas. Good question - this is a text result from an SQL column from which we are trying to extract part of the value. Due to the peculiarities in the way insurers report to their underwriters we've having to generate an 85 column list in a text file on a daily basis that is transferred via SFTP. ExecuteSQL is perfect for this as we can also trap for unwanted line ends/carriage returns by using an unusual row separator, then substitute before outputting.


                      We're using variations of LEFT, SUBSTR and STRVAL as columns have a finite length due to the system that will be importing the text files and any variations to the specified structure will break the receiving system. We just seem to be short of PATINDEX or similar to achieve our results without resorting to yet another field addition within FileMaker.


                      Kind regards



                      • 8. Re: SQL Extract Number from a String



                        But as previously mentioned, this has to be done in the SELECT statement, not using FileMaker functions.


                        All the best



                        • 9. Re: SQL Extract Number from a String

                          Just to clarify, we have had to add an additional field within FileMaker just for this report. Up until now, there has been no need to extract any numeric from the source field, it is just a value list that triggers a percentage calculation.


                          Perhaps we'll be lucky and get a few more SQL functions to play with in the future.


                          Hope everyone had a good Christmas



                          • 10. Re: SQL Extract Number from a String

                            I know you stated this;-) But as I read in your reply to Beverly's post, you do post sql-processing anyway (substitute...), so why not this one?



                            • 11. Re: SQL Extract Number from a String



                              Appreciate your input. The problem is there are 85 columns and this one is buried within something similar to the following extract:


                              "'', " &  //Notes (39)

                              "'EAP', " &  //System Defined product ID (40)

                              "'', " &  //Plan ID (41)

                              "SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateStart ) & ") ) , 1, 4 ) + SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateStart ) & ") ) , 6, 2 ) + SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateStart) & ") ) , 9, 2 ), " & //Effective Date (42)

                              "SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateEnd ) & ") ) , 1, 4 ) + SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateEnd ) & ") ) , 6, 2 ) + SUBSTR ( STRVAL ( (p." & SQLField ( Policies::kDateEnd ) & ") ) , 9, 2 ), " & //Terminiation Date (43)

                              "'BR', " &   //Premium Group ID (44) - BROKER AGENCY NO

                              "'', " &  //Product Code (45) - TBA

                              "'', " &  //Premium Group Department ID (46)


                              84 of the 85 columns can be extracted using SELECT. I do agree I could look for the 2 pipe parameters we're using, which would be pipe 78 and 79 (column 79), and piece together each row using FileMaker functions, but to be honest it is just easier to pop a dedicated field in. It would be so much neater to just use the SQL to generate the data - the functions do exist, but are not supported by FileMaker.


                              The only post sql processing is that we're not using a pilcrow as a row separator, so we can remove any unwanted line endings and then put the row separators back in, but this is just taking advantage of the ExecuteSQL parameters then using a blanket FileMaker Substitute, not pulling the result apart and reconstructing it.


                              Kind regards


                              • 12. Re: SQL Extract Number from a String

                                I agree, the calculation field is a simple solution, I do it this way often. But did you have a look at the CF, iIt takes the column and the column-delimiter as a parameter. the only thing is, it needs the "¶" as row-delimiter. But I assume, you replace the "¶" in values with something else anyway, so it should work, except your result has to many rows and exceeds the max recursion, then you would have to split it.


                                best regards


                                • 13. Re: SQL Extract Number from a String

                                  1. the "FMP15 SQL Reference" may be used with (p. 6):

                                       a. FileMaker database as a data source

                                       b. the ExecuteSQL function


                                  2. the ExecuteSQL() function use a small subset of the capabilities as found in the above guide


                                  3. there are additional SQL calls that may be made on an external SQL source (as connected via ODBC/JDBC) through ESS (approved sources) or Import script step.


                                  4. it's not 'cheating' to use other native FileMaker functions on any data as FileMaker sees it on the Relationship graph (whether imported or ESS - view of SQL table - or native FM table).


                                  Andy, are you using Import or ESS on a SQL database and getting the data into FileMaker and then performing the ExecuteSQL() to extract your data? Because you may have more SQL ability with:

                                       a. one of the FileMaker SQL plug-ins

                                       b. using the Import on an ODBC/JDBC source - using only the SELECT


                                  Really using FileMaker for what FileMaker does extremely well with its functions is going to be far better than relying on one function - ExecuteSQL().

                                  • 14. Re: SQL Extract Number from a String

                                    Hi Beverly


                                    No, we're just needing to extract a whole bunch of info out of FileMaker. These days we're focussing on performance for day to day use and then try to generate reports dynamically to remove the need for summary fields, cross table calculations, additional TO groups, dedicated report layouts, etc. as much as possible. Our view is that if you have n no. of users entering data (in this case multiple countries, currencies, taxes), then their productivity is reduced by n x the additional time it takes to update fields only used within reports.


                                    We tend to build management information reports using dedicated tables, creating records and populating fields using scripted calculations on demand as this is usually only one person at a time and people tend to be more tolerant waiting for complex reports to be presented, rather than when entering data or performing simple finds. FileMaker's inability to Save as Excel by honouring the hide and off screen objects further reduces the ability to generate generic reports (Export has to be defined each time).


                                    I suspect we've previously used just about every trick in the book, as the systems we write often seem to 'push FileMaker's envelope', but in this particular case, we had to resort to a dedicated field, as ExecuteSQL did 99% of what we wanted, that allowed us to set a text field to a variable result and export it for the required purpose. The alternative would have required many of the things above that we try to avoid.


                                    Thanks for the input