FileMaker Pro 9: The Missing Manual

Text Parsing Calculations

Although most people think of functions for doing dry stuff like math in a spreadsheet, you can also use functions in your database’s text fields. Just as you can add and subtract numbers with number functions, you can use text functions to slice and dice the words in your database. For example, you might receive data from an outside source that needs major cleanup before you can use it. This data has people’s first and last names in the same field; it’s even got entire email messages crammed into a field—address, subject, and body—when all you need is the email address. You can equip a temporary database with fields and text calculations to parse (think of it as sifting) the data into the form your better-designed database expects.

Note

Fixing data this way usually means that you do a find for a certain kind of bad data—if only some records have two email addresses in the same field, say. Use a calculation with the Records → Replace Field Contents command. Do a find first, and then do a calculated Replace Field Contents that fixes the error.

The Concatenation Operator

In contrast to the wide variety of mathematical operators for working with numbers, there’s only one that pertains specifically to text—the concatenation operator. Represented by the & sign (ampersand), it strings bits of text together. (When you need to chop and divide your text in order to parse it, you use a function instead of an operator, as described on Text Parsing Calculations.)

To use ...