Thursday, August 14th, 2008
In part 1 of this series, we looked at the basics of using calculated fields, including referencing fields and using functions.
In this installment, we'll look at how you can use calculated fields to extract data values from a column with “dirty” data. This happens more than one might think: if you've ever "cleaned" a list of contact information or migrated databases, you know that you often have to take a column and break it up into multiple parts. Calculated fields make this task very easy.
Let's look at an example. Suppose we have a table that contains raw contact information smashed together in a single field:
Let's extract each of these parts — first name, last name and email address — from the single column into multiple columns. We'll do this by using a calculated field with a function that parses the string, based on a delimiter (such as a space), and returns the relevant portion of the string. (more…)