1 2 3 4 Previous Next 45 Replies Latest reply on Apr 11, 2017 1:54 PM by fmpdude

    Extracting Uppercase words from a text field

    PeterWindle

      Would anyone know how to go about extracting only uppercase words from a field?

       

      I'm thinking this might be a custom function?? Anyone know what is required?

        • 1. Re: Extracting Uppercase words from a text field
          CamelCase_data

          At least for a simple and quick implementation (there can be others that perform better), I would use the CustomList custom function (http://www.briandunning.com/cf/868) with a formula along the lines of:

           

          CustomList (

           

          1 ; WordCount ( $Text ) ;

           

          "

          Case ( 

            Exact (

            MiddleWords ( $Text ; [n] ; 1 ) ;

            Upper ( MiddleWords ( $Text ; [n] ; 1 ) )

            ) ;

          MiddleWords ( $Text ; [n] ; 1 )

          )

          "

           

          )

          2 of 2 people found this helpful
          • 2. Re: Extracting Uppercase words from a text field
            fmpdude

            Problems like this are another reason Regular Expression matching is so needed in FMP (say back in version 2 or 3).

             

            A simple regular expression like the one below can do the trick:

             

            And, if you wanted to only include words that were all capitalized, you could add a simple modification to the RegEx:

             

            Regular expressions then obviously also need to be integrated with "Find" commands so you can match by RegEx.

             

            ------

             

            Non RegEx:

             

            In the "Data Viewer", FileMaker says these two are equal:

             

            "THIS" = "this"  (Result 1)

             

            thus, making simple case-sensitive comparisons difficult (after you do the drudgery of breaking up the field words into tokens). This may be an area where I don't know how to tell FileMaker "Hey, make this a case-sensitive comparison!"

             

            ---


            A simple RegEx does it (that is, could/would do it in some future version of FMP) all quickly and in one step!

             

            MySQL, SQL Server, and just about every other database have RegEx support right in SQL making this kind of task simple.

            1 of 1 people found this helpful
            • 3. Re: Extracting Uppercase words from a text field
              CamelCase_data

              I certainly agree that native regular expressions would be great!

               

              However, FileMaker's Exact and Upper functions do have the advantage of properly handling uppercase vs lowercase characters in pretty much any language that has that distinction, so it may not be the ideal choice here.

               

              A simple ([A-Z]) will e.g. choke on my last name (WIKSTRÖM).

              1 of 1 people found this helpful
              • 4. Re: Extracting Uppercase words from a text field
                fmpdude

                Of course, all you need to do is add that character to the RegEx. I also made sure you only match whole words.


                There, done. Simple.

                When it comes to programming, (OK, using Java) I can even extract all the upper case words from a text file in 0.5 seconds. So, for this task, I'd be tempted to write a micro-service and quickly solve the OP's problem - without looping scripts in FMP, for example.

                 

                The following one line of code searches a 500,000 names text file (names.csv) in 0.45 seconds and extracts all the upper cased names:

                 

                Files.lines(Paths.get("names.csv")).

                   filter(s -> s.matches("^\\w+([A-Z])+\\s*[A-Z]+")).

                   sorted().

                   parallel().

                   forEach(System.out::println);

                 

                Note this code is "declarative" (like SQL), which is a HUGE deal, not "imperative" (as in procedural code). That is, there is zero, none, nada, coding required.  You specify "what" you want, and the engine figures out how to get it. In this case, also, the ".parallel()" uses all available processor cores.

                 

                The result from my test run:

                 

                JIMMY CONNERS

                JACK SOCK

                RODGER FEDERER

                (0.45 seconds needed to RegEx search a 500,000 names text file.)

                 

                So, a little microservice FMP calls would be something I'd definitely consider here.

                 

                ---

                 

                Using RegEx in SQL is just as simple.

                2 of 3 people found this helpful
                • 5. Re: Extracting Uppercase words from a text field
                  rgordon

                  If want to do this with a simple looping script this script should give you what you want.caps.png

                  • 6. Re: Extracting Uppercase words from a text field
                    alecgregory

                    Well yes that works for Ö but there's a tonne more characters you'd have to add in too for the RegEx to be useful in this case.

                     

                    Regular Expressions can be handy but they are definitely not simple! Non-trivial regular expressions are often impossible to understand at a glance and very hard to understand when looked at in detail. As Douglas Crockford says:

                    The rules for writing Regular Expressions can be surprisingly complex because they interpret characters in some positions as operators and in slightly different positions as literals. Worse than being hard to write, this makes regular expressions hard to read and dangerous to modify.

                    So I can understand why FMI do not include them in the core FileMaker feature set. There's always plug-ins or a Web Viewer if you really need to use RegEx. I find that I very rarely have that need.

                    • 7. Re: Extracting Uppercase words from a text field
                      fmpdude

                      Sure, you would need to include the characters in the RegEx or wild-card them using a range of Unicode characters.

                       

                      Having the RegEx tool might not be for everyone, but if it were there, it would be used by many, many devs.

                      • 8. Re: Extracting Uppercase words from a text field
                        alecgregory

                        fmpdude wrote:

                         

                        Having the RegEx tool might not be for everyone, but if it were there, it would be used by many, many devs.

                        I guess that's where we differ. I don't think it would be used by many devs.

                        • 9. Re: Extracting Uppercase words from a text field
                          fmpdude

                          Interesting.

                           

                          How long does that code take to run with, say, 100,000 rows?

                          • 10. Re: Extracting Uppercase words from a text field
                            fmpdude

                            You may be right.

                             

                            Not meaning to argue with you, but RegEx is a common tool used almost everywhere. Pick any serious editor and it's there. Any IDE (even searching code within the IDE itself, it's there. Look at any SQL, RegEx is there. Just about any programming language? Yep, RegEx is there, too.

                             

                            So, if you don't need it, don't use it. There's lots of stuff I don't use in FMP. But when RebEx is useful, RegEx can be a quick elegant way to solve a problem. Read: QUICK. RegEx is also declarative: No (imperative) coding needed. Tons of online resources and tools to help craft a RegEx (and folks like me and others here who could help, also).

                             

                            ---

                             

                            In any case, using INSERT FROM URL or CURL, the OP can still do what he needs quickly, externally, if needed.

                             

                            I don't know enough about the OP's overall context/requirements to argue one way or another.

                             

                            I like it that FMP is open to allow externalization if needed.

                            1 of 1 people found this helpful
                            • 11. Re: Extracting Uppercase words from a text field
                              beverly

                              I'll disagree with you alecgregory! I think if we had RegEx, it would be used. There may be a need to train a few people, but that's true of many of the functions we have now.

                              • 12. Re: Extracting Uppercase words from a text field
                                alecgregory

                                I'd certainly be happy to see RegEx fluency in this community. My fear is that it would be quite badly misused and mess up people's solutions leading them to get frustrated with FileMaker. It's fairly easy to write a RegEx that works 90% of the time and then fails rather horribly the other 10%. However, I guess that is the case with a few FM functions that are widely used!

                                • 13. Re: Extracting Uppercase words from a text field
                                  fmpdude

                                  I guess your argument, if that's what it is, could apply to scripts, too...

                                   

                                  With every capability there is a learning curve. But, imagine the time savings.

                                   

                                  Here is the same query which took 0.45 seconds, using Java, to search a 500,000 line text file but applied to a MySQL table:

                                  No coding necessary.

                                   

                                  Declarative: Just specify the "what", no need to specify the "how" (as in scripting)

                                   

                                  Fast.

                                   

                                  Easy.

                                   

                                  --

                                   

                                  Still want to write a script and loop through 500,000 rows?

                                   

                                  Sure, go ahead.

                                   

                                  Please post back how long that code takes to (1) write, and (2) run the code each time.

                                  • 14. Re: Extracting Uppercase words from a text field
                                    beverly

                                    As is true for:

                                    ExecuteSQL()

                                    and other complex functions (and functionality) that take the developers outside "common" knowledge.  Let's not dismiss something that is not common in FM, but is common elsewhere! We can train each other, that's a huge function of this forum!

                                     

                                    And my mantra before changing any data (with any function - even the easy ones):

                                    backup, BackUp, BACKUP

                                     

                                    beverly

                                     

                                    p.s. BTW there is a demo (.fp7) of a way to do _some_ regEx in FileMaker with native & custom functions:

                                    * FileMaker 7 Examples

                                    Perl-Style Regular Expressions inside FM Pro 7 - A group of custom functions that implement a regular expression matcher.

                                    Thank you, Shawn Flisakowski! (check some of the other demos - they will convert to FMP 15)

                                    1 2 3 4 Previous Next