Database Reference
In-Depth Information
NOTE
If your text value doesn't have normal words (like a long URL, for example), then you may have to
pay special attention to the letters-numbers-periods rule to get the results you expect.
Along the same lines as the character functions, FileMaker has three word-oriented functions
called
LeftWords()
,
RightWords()
, and
MiddleWords()
. You can use a word function to
parse a person's first and middle names if you ever get a file with all three names unceremo-
niously dumped into a single field.
▪
LeftWords()
returns all the text before the end of the specified word. For instance, this
function:
LeftWords ( Preamble ; 3 )
might return
We the People
. But if Preamble contained “This *** Is *** a *** Test,” it
would return
This *** Is *** A
instead. In other words, it doesn't just return the words. It
returns
everything
before the end of the third word.
▪ Likewise,
RightWords()
returns everything
after
the specified word's
beginning
, count-
ing from the end. This calculation:
RightWords ( MagnaCarta ; 1 )
returns
reign
.
▪ What would
LeftWords()
and
RightWords()
be without
MiddleWords()
? You can
probably guess how this function works: You pass it a text value, a starting word, and the
number of words to return. It then returns everything from the beginning of the starting
word through the end of the finishing word. The following calculation shows how it
works; it returns
or not
because they're the third and fourth words.
MiddleWords ( "To be, or not to be" ; 3 ; 2 )
Text Editing Functions
FileMaker includes dozens of text functions, but a few of them are worth special mention be-
cause you see them throughout the rest of this section, and because they're so useful for
cleaning up messy data.
▪ The
Substitute()
function performs a find-and-replace within a text value. For ex-
ample, if you want to turn all the x's to o's in your love letter (maybe you felt like you
were coming on too strong), then you can do this:
Substitute ( Love Letter ; "x" ; "o" )
