Applying String Manipulation

Objectives

After completing this lesson, you will be able to:

  • Manipulate strings in a report

String Manipulation Overview

When creating a report, you often want to embed the information from several string fields together as one field. For example, a customer’s first and last name fields can be combined to be one complete name field. In this way, formatting and moving this single field around the report is easy.

By using formulas to manipulate the data contained in string fields you can get exactly what you need from your database.

String Manipulation Formulas

String manipulation formulas include:

  • Subscript

  • Concatenation

  • Left

  • Mid

  • Right

Subscript

When you want to use only a certain part of a string field, you can extract characters from the string field using the subscript function.

For example, you want to use only a first initial of a contact name instead of the entire name, or you want to extract several characters from a string field. In the first example, you would create the formula:

Code snippet
{Customer.Contact First Name}[1]
Expand

Returns "A", where the Contact First Name is Anne-Marie.

When extracting several characters from a string field, you would create the formula:

Code snippet
{Customer.Contact First Name}[1 to 4]
Expand

Returns "Anne", where the Contact First Name is Anne-Marie

Concatenation

When you want to join two or more components together to form a singular continuous string, you would use one of these concatenation symbols:

  • &

    Enables you to join fields of different data types.

  • +

    Enables you to join string fields.

For example, if you want to add descriptive text to your data field, you would write the formula:

Code snippet
"This product was shipped on " & {Orders.Ship Date}
Expand

The Left Function

Left (str, length)

The Left function consists of a text string (str) and length and obtains a certain number of characters from the left end of a text string. For instance, you could use the Left function to obtain just the area code from the values in a field containing phone numbers. You would write the formula:

Code snippet
Left({Customer.Phone}, 3)
Expand

This formula would retrieve the first three numbers from the phone number, which is typically the area code.

The Mid Function

The Mid function returns a specified number of characters from a string. The second argument is the character position where the part to be taken begins. The optional third argument is the length of the string that you want to be taken out. If the third argument is not specified, everything from the start position to the end of the string is extracted. Use this function when you must extract a set of characters from somewhere in the middle of a text string. For example, you must extract the middle two characters of a field as an identifier for customers.

Code snippet
Mid({Customer.CustomerName}, 3, 2)
Expand

This formula returns a two–character substring from the inside of the string starting with the third character position. In the case of Alley Cat Bikes, the identifier is "le".

The Right Function

The Right function extracts the given number of text characters from the right side of the specified string. Use this function to obtain just the right part of the string. For example, you must obtain the last four digits of social security numbers stored in a field as an employee identifier.

Code snippet
Right({table.SSNUM}, 4)
Expand

This formula returns the last four digits of the social security number as a string.

Manipulate Data Using Formulas

Log in to track your progress & complete quizzes