Manipulating Strings

It is common for a string to be modified before it is displayed on a report. This can consist of reformatting the string or even joining the different elements of an array into a single string. Table 6-4 shows the functions for manipulating strings. Descriptions of each function are listed next to its name. The functions Filter(), Split(), and Picture() are more complex and are explained in more detail after the table.

Table 6-4. String Manipulation Functions

The & or + is used between two string variables (or fields) to combine them into a single string. An example is:

{Customer.LastName} & “, ” & {Customer.FirstName};

Although you can use + to concatenate strings, it is recommended that you use & because it is most commonly recognized at the standard operator for concatenating strings. Using + can also be confused with arithmetic functions.

If you want to use the quote literal in a string (“), this can be a little challenging at first. Since the quote represents the beginning and end of a string literal, then when you try to insert it in the middle of a string it gets Crystal Reports confused and thinks you are trying to end the string twice. There are two ways to put the quote into a string literal. The first is to repeat it twice in the string and Crystal Reports will translate this into a string literal. Here is an example:

// Display “Ben Hur”
“””Ben Hur”””

The first quote tells Crystal Reports that you are starting a string literal. Then it sees two quotes in a row and replaces them with a single quote in the string. The effect is that there is a quote at the beginning and end of the string. The next example is a little less confusing because it puts the quote in the middle of the string.

// Display The quote (“) can be confusing
“The quote(“”) can be confusing”

An easier way to insert the quote into a string is to use the Chrw(39) function. This returns the quote literal and it is much easier to read.

// Display “Ben Hur”
Chrw(34) & “Ben Hur” & Chrw(34)

The Filter() function searches an array of strings for a matching sub-string. It returns an array of all the strings that have that sub-string in them. The first argument is the string array and the second argument is the sub-string to search for. Essentially, this function calls the InStr() function for every string in the array. If the InStr() finds a match, the string is added to the result array.

The Filter() function has an optional include argument that tells the function to return an array of strings that don’t match the sub-string. Essentially, this would be the same as saying that it returns all strings where the InStr() function returns a zero. Pass the include argument the value False to get an array of the strings that don’t have the sub-string in them. If you don’t pass a value for this argument, then the default value of True is used. The next listing demonstrates using the Filter() function with different arguments.

//Demonstrate the Filter() function
StringVar Array StringArrayVar;
StringVar Array ResultArrayVar;
StringArrayVar := MakeArray(“abcd”, “bcde”, “cdef”);
//This will return an array with two elements: “abcd”, “bcde”
ResultArrayVar := Filter(StringArrayVar, “bc”);
//This will return an array with one element: “cdef”
//This is because it is the only element that doesn’t have the sub-string
ResultArrayVar := Filter(StringArrayVar, “bc”, False);

The Replace() function searches for a sub-string within another string, and if it finds it, then it replaces it with the new string. It uses an additional optional argument called count. The count argument lets you limit how many string replacements are done. If you pass a number for this argument, then the number of replacements done cannot exceed that value. If you don’t pass a value for this argument, then all the sub-strings are replaced.

//Change the addresses so that they use abbreviations
StringVar Streets;
Streets := “123 Main Street, 456 Cherry Avenue, 999 Brook Street”;
Streets := Replace(Streets, “Street”, “St.”);
Streets := Replace(Streets, “Avenue”, “Ave.”);
//Streets is now “123 Main St., 456 Cherry Ave., 999 Brook St. “
Streets;

The Split() and Join() functions work together nicely. The Split() function takes a string and splits it into a string array. The string is separated based upon a character you pass to the function. This is typically a comma, but it can be anything you need it to be. Splitting the string apart makes it is easy to work on the individual strings. After you are done making any necessary changes to the individual strings, you can combine them back into one string using the Join() function. How convenient!

The next example demonstrates combining the functionality of the Split() and Join() functions. A string with the names of customers is available and each name is separated with a comma. We want to only select strings where the names have a prefix of “Mr.” This is done by splitting the names into an array of strings. Then the Filter() function is used to return an array with only the strings that match our criteria. This array is combined back into a comma-delimited string using the Join() function.

//Demonstrate the Split() and Join() functions by finding the names prefixed with “Mr.”
StringVar Names;
StringVar Array NamesArrayVar;
Names := “Mr. Jones, Sir Alfred, Ms. Bee, Mr. Smith”;
NamesArrayVar := Split(Names, “,”);
//Get the names that only use Mr.
NamesArrayVar := Filter(NamesArrayVar, “Mr. “);
//RJoin the array back into a comma-delimited string
Names := Join(NamesArrayVar, “,”);
//Names is now “Mr. Jones, Mr. Smith”
Names;

The Picture() function maps a string onto a template. The first argument is the source string and the second argument is the template.

The template consists of a series of “x”s with other characters around it. Each character in the source string gets mapped onto each of the “x”s in the template. The source string can use any character and it will get mapped. If the source string has more characters than can fit in the template, then all remaining characters are added to the end. If the template has any non-“x” characters, then they stay as they are.

//Demonstrate mapping a string with non-alphanumeric characters
Picture(“ab&[{1234”, “xxx..xx..x.. “)
//The result is “ab&..[{..1..234”

This example illustrates that all characters in the source string were mapped onto the “x”s. It also shows that since the source string has nine characters and the template has six “x”s, then the extra three characters are added to the end.

Question: When I use a text object to mix text with database fields, Crystal Reports lets me apply different formatting properties to each field. But, when I put a string formula on the report, the entire string has to use the same formatting. Is there a way to apply different formatting to parts of a string?

Answer: Yes, you can do this by including HTML formatting tags within the string. For example, the following formula puts bold emphasis on the address field:

"Hello " & {Customer.Name} & ". Are you still located at " & {Customer.Address} & "?";

After adding the formula to the report, right-click on it and select Format Field. Go to the Paragraph tab and set the Text Interpretation property to HTML. Using HTML formatting within a string formula gives you great flexibility for modifying its output.