10 Replies Latest reply on Jul 27, 2012 5:38 PM by disabled_JustinClose

    MiddleWords separator character?

      Title

      MiddleWords separator character?

      Your post

      Is there a way to suppress a certain word separator?  I am trying to parse the results of an ExecuteSQL query into separate fields.  Using MiddleWords() would work well in a loop, except that some of my data may contain spaces:

      middlewords("14|Delivery|Production Coordinator|23"; 3 ; 1 )

      Returns "Production" only.  (I was going to use counters in place of the 3 and 1 for the loop.)  I have multiple lines returning, so don't want to use a CR or something as the record separator.  I used pipe just because I like em, and they are usually easy to handle.  Hand't realized, at the time, that it would work as a word separator for ###Words() functions.

      Any suggestions about how to handle this?  Or better ways to parse the resulting SQL string?

       

      Thanks,

      J

        • 1. Re: MiddleWords separator character?
          philmodjunk

          You can't change what characters are recognized as a word separator, you must either use a different expression or modify the data so that that space character does not occur.

          Example

          Let ( TNoSpace = Substitute ( "14|Delivery|Production Coordinator|23" ; " " ; "_" ) ;
                  Substitute ( MiddleWords ( TNoSpace ; 3 ; 1 ) ; "_" ; " " )
                )

          And you have many choices for what character to substitute in place of the space. The trick is to pick one that will never occur in the text you are parsing as that will break this method. There are some very unlikely to be used characters that you can use for this instead of the underscore I used in this example.

          • 2. Re: MiddleWords separator character?

            Well bummer.  I was hoping that there was a way of changing the default delimiter, or of specifying what delimiter you want to use.  (The 'default delimiter' is actualy quite the long list of characters.  Here is the actual help text:

             

            FileMaker Pro recognizes the following characters (and others) as wordseparators when working with the functions WordCount, RightWords, LeftWords, and MiddleWords:

            space ! @ # $ % ^ & * ( ) _ + = { } [ ] | \ : ; " ' < > ? / * ~

            The following characters (and others) do not act as wordseparators:

            0 1 2 3 4 5 6 7 8 9 Ï a b c, etc. A B C, et.c

             

            Thanks for the pointers on methodology, Phil.  I like what you have put there, although perhaps still need to find a different character.  It is a bit more straightforward than the idea I was mulling around about using a few position() calls to get the 2nd and 3rd pipe, and then doing a middle() with those.

             

            Thanks,

            J

            • 3. Re: MiddleWords separator character?
              philmodjunk

              There are some other options to use instead of _, The § character is one I've seen used in other example FileMaker Expressions.

              • 4. Re: MiddleWords separator character?

                I was looking more closely at the list of 'word separator characters' and they list every single possible character available on my keyboard, at least the various usual key-combo ones, i.e. un- and shifted- ones.  Seems kind of odd that they would include all of those. 

                I went ahead and used a position() and middle() technique.  I then figured that I might need to do this a lot in the future, so made a custom function out of it.  I haven't put in a ton of thought about making it all kinds of secure and bullet proof; I did try and plan for various possible conditions and edge cases.  Here is what I came up with:

                Let ( [
                   TheStart = Position ( SomeText ; Delim ; 1; nth-1 ) + 1;
                   TheEnd = Position ( SomeText ; Delim ; 1; nth );
                   chars = TheEnd - TheStart;
                   TextLen = Length ( SomeText );
                   maxDel = PatternCount ( SomeText; Delim )
                ] ;

                Case (
                    nth <= 0 ; "" ;
                    nth > maxDel + 1 ; "" ;
                    nth = maxDel + 1 ; Middle ( SomeText; TheStart; TextLen + 1 - TheStart);
                    //nth = maxDel;  Middle ( SomeText; TheStart; TextLen + 1 - TheStart);
                    Middle ( SomeText; TheStart; chars)
                )   // End Case
                )   // End Let

                 

                I may go back through and implement something along the lines of what you originally suggested.  That would certainly make the function a good bit shorter.

                 

                Thanks

                J

                • 5. Re: MiddleWords separator character?
                  philmodjunk

                  I see what you mean, the underscore is a word separator as is most other symbols you might use.

                  I did get this one to work, however:

                  Let ( TNoSpace = Substitute ( "14|Delivery|Production Coordinator|23" ; " " ; "ä" ) ;
                          Substitute ( MiddleWords ( TNoSpace ; 3 ; 1 ) ; "ä" ; " " )
                        )

                  I used Insert Symbol in MS Word to insert the character on a word page and then copy/pasted into the Filemaker Data viewer to test it.

                  If I were to use your other method, I'd use this expresson:

                  Let ( [T = "14|Delivery|Production Coordinator|23" ;
                           start = Position ( T ; "|" ; 1 ; 2 ) + 1 ;
                           end = Position ( T ; "|" ; 1 ; 3 )
                         ];
                         Middle ( T ; start ; end - start )
                        )

                  • 6. Re: MiddleWords separator character?

                    Here is a version I ended up with after employing your suggested Substitute() method:

                    Let ( [
                        _Text = Substitute ( GetValue( SomeText; 1)  ; " " ; "ä" );
                        _maxDelim = PatternCount ( _Text; Delim )
                     
                    ] ;


                    Case (
                        nth < 1 ; "" ;
                        nth > _maxDelim + 1 ; "" ;
                        Substitute ( MiddleWords ( _Text ; nth ; 1 ) ; "ä" ; " " )
                    ) // End Case
                    )   // End Let

                     

                    It is quite a bit shorter.  :)

                    I will try and dig up another character to put in there; that one is possible in non-English words.  Just trying to be a bit more universal/portable.

                    • 7. Re: MiddleWords separator character?
                      philmodjunk

                      Presumably this is a custom function where SomeText, Delim and nth are the parameters?

                      Seems like you'd have a problem if nth = 3, and MaxDleim = 2... I think you'd want to use nth > _maxDelim instead of _MaxDelim + 1.

                      you might experiment with using the Char function to use to get a can't be entered character that isn't also a word separator, but I still think that this expression:

                      Let ( [T = "14|Delivery|Production Coordinator|23" ;
                               start = Position ( T ; "|" ; 1 ; 2 ) + 1 ;
                               end = Position ( T ; "|" ; 1 ; 3 )
                             ];
                             Middle ( T ; start ; end - start )
                            )

                      might be much simpler and less likely to encounter trouble with strange text than using middlewords to extract the desired text.

                      • 8. Re: MiddleWords separator character?
                        philmodjunk

                        It could be set up as a CF something like this:

                        //ParseText ( TheText ; Delim, nth )
                        //
                        // TheText : text to be parsed
                        // Delim: Character to serve as delimitter between sections of text
                        // nth: The position of the text to be extracted out from between delimitters
                        //
                        Let ([T = TheText & Delim;
                               start = Position ( T ; Delim ; 1 ; nth - 1 ) + 1 ;
                               end = Position ( T ; Delim ; 1 ; nth )
                             ];
                             Case ( nth + 1 > PatternCount ( T ; Delim ) ; "" ; // not enough delimitters for specified position
                                       T = Delim  ; ""  ; No text to parse ;
                                       Middle ( T ; start ; end - start )
                                      ) // case
                              ) // let

                        • 9. Re: MiddleWords separator character?

                          Yes, this was a custom function.  Yes, "SomeText", "Delim", and "nth" are parameters to the function. 

                          For this line:     

                          nth > _maxDelim + 1 ; "" ;

                          I used "_MaxDelim + 1" because there are more elements than delimiters.  For 3 elements,  _maxdelim = 2.  nth = 3 is a valid element, so I don't want to return an empty string by using:      nth > _maxDelim ; "" ;

                          It looks like in your last suggestion you tacked an additional delimiter onto the text.  That would allow you to get away from the +1, as well as making the position() technique you show work more easily.  I like that technique...

                          But it looks like your statement will miss the last element:

                          Case (

                          nth + 1 > PatternCount ( T ; Delim ) ; "" ; // not enough delimitters for specified position
                          T = Delim  ; ""  ; No text to parse ;
                          Middle ( T ; start ; end - start )

                          ) // case

                           

                          Take 3 elements, then you will have 3 delimiters (since you tacked on an additional one in an earlier step).  Then if nth = 3 (a valid request):

                          nth + 1 > PatternCount( T; Delim)  =  True, giving you an empty string.  Still need a +1 on delim count, or remove the +1 on nth, or remove current + 1 and use "=" instead of ">" in the conditional, etc.

                          I like the additional check for missing text.  I will have to add that in there.  I agree that this would also be simpler and more robust.  Thanks for all the feedback.

                           

                          -- J

                           

                           

                           

                          • 10. Re: MiddleWords separator character?

                            Getting back to this a bit, here is what I currently have in place for my custom function:

                             


                            Let ( [
                                _T = GetValue( SomeText ; 1 ) & Delim;     //Gets just first line of input text, appends an extra DELIM on end
                                _start = Position ( _T ; Delim ; 1 ; nth - 1 ) + 1 ;
                                _end = Position ( _T ; Delim ; 1 ; nth ) ;
                                _maxDelim = PatternCount ( _T; Delim ) 
                            ] ;


                            Case (
                                nth < 1 ; "" ;
                                nth > _maxDelim ; "" ;
                                _T = Delim  ; ""  ;     //Nothing in the string but the delimiter added in by this function.
                                Middle ( _T ; _start ; _end - _start )
                            ) // End Case
                            )   // End Let

                             

                            Seems to be working well.  Thanks for the input, Phil.

                            --  J