Applying String Manipulation

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

1
{Customer.Contact First Name}[1]

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

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

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

1
"This product was shipped on " & {Orders.Ship Date}

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

1
Left({Customer.Phone}, 3)

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

1
Mid({Customer.CustomerName}, 3, 2)

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

1
Right({table.SSNUM}, 4)

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

Applying String Manipulation

String Manipulation Overview

String Manipulation Formulas

Subscript

Concatenation

The Left Function

The Mid Function

The Right Function

Manipulate Data Using Formulas