Text Functions

In the sections below, the calculated attribute text functions are grouped by purpose:

Escape Functions can be used to allow for special characters during text manipulation.
Identify Functions can be used to display the defined data.
Modify Functions can be used to modify defined data.
Regular Expression Functions can be used to search and modify text based on a defined template.
Representative Text Functions can be used to produce an alternative value for the defined text.
Test Functions can be used to perform a test, and return a result.

For examples and scenarios using these functions, refer to the list of Use Cases at the end of each section or refer to Text Examples here.

Escape Functions

These functions can be used to allow for special characters during text manipulation.

Escape Function	Parameters	Description
ESCAPEMERGE	(split-character, escape-character, text1, ...)	Merges text1...textN into a single string. The 'split-character' is used as a separator between the elements. If the 'split-character' is found in the text elements, then the 'escape-character' precedes it. Opposite function of ESCAPESPLIT.
ESCAPESPLIT	(split-character, escape-character, text)	Takes a single string and splits it into a list of elements, using the 'split-character' in the string, and removing the 'escape-character' after splitting the elements. To remove duplicates in a string, use ESCAPESPLIT to split a single text to multiple elements. Then use the UNIQUE function to remove the duplicates. Afterward, use LISTCONTATENATE to return the list elements to single string. Opposite function of ESCAPEMERGE.

Escape Function

Parameters

Description

ESCAPEMERGE

(split-character, escape-character, text1, ...)

Merges text1...textN into a single string. The 'split-character' is used as a separator between the elements. If the 'split-character' is found in the text elements, then the 'escape-character' precedes it.

Opposite function of ESCAPESPLIT.

ESCAPESPLIT

(split-character, escape-character, text)

Takes a single string and splits it into a list of elements, using the 'split-character' in the string, and removing the 'escape-character' after splitting the elements.

To remove duplicates in a string, use ESCAPESPLIT to split a single text to multiple elements. Then use the UNIQUE function to remove the duplicates. Afterward, use LISTCONTATENATE to return the list elements to single string.

Opposite function of ESCAPEMERGE.

Identify Functions

These functions can be used to display the defined data.

Identify Function	Parameters	Description
LEFT	(text [, len]):	Returns the first ‘len’ number of characters of the text. [len] is optional, the default is 1.
LEN	(text)	Returns number of characters in ‘text’.
MID	(text, pos, len)	Returns the substring of ‘text’ starting at character position ‘pos’ with length ‘len’.
RIGHT	(text[, len])	Returns the last ‘len’ number of characters of the text. [len] is optional, default is 1.

Modify Functions

These functions can be used to modify defined data.

Modify Function	Parameters	Description
CONCATENATE	(text1, text2, ...)	Returns the concatenated text of all the supplied texts in the parameter string. There is no limit to the number of strings that can be concatenated. Not typically used with values only; more often used with text strings to display values appropriately.
FIRSTNAMEFIRST	(text)	Function for reorganizing names formatted with family name first followed by a comma and then first name.
LOWER	(text)	Returns 'text' where all characters are in lower case.
PROPER	(text)	Returns 'text' where the first letter of all words in the text string (and the first letter that follows any character other than a letter) is capitalized. Converts all other letters to lowercase letters.
REPLACE	(text, pos, len, newtext)	Returns ‘text’ where the substring starting at character position ‘pos’ with length ‘len’ has been replaced with the text ‘newtext’. Also consider the SUBSTITUTE function below within this topic. This function is very strict in that the actual location and number of the characters to be replaced must be specified. Miscalculation of the position and length of replaced characters can result in an undesired outcome. It is a good idea to test your value template using the Evaluate feature within the Function Editor dialog.
SUBSTITUTE	(text, from, to [, indexno])	Returns ‘text’ where all instances of text ‘from’ are replaced with text ‘to.' Comparable to typical 'find and replace' functionality in a standard text editor. Also consider the REPLACE function above within this topic. [indexno] is optional. If supplied, only that one instance is replaced. Typically only practical to use for the first instance.
TRIM	(text)	Returns 'text' where leading and trailing spaces are removed.
UPPER	(text)	Returns 'text' where all characters are in upper case.

Use Cases

For a scenario using the CONCATENATE function, refer to Total Cost of Materials Scenario here.

Additional use cases for the CONCATENATE function are included in the Test Functions section below.

Regular Expression Functions

These functions can be used to search and modify text based on a defined template.

Use '\n' for producing a new line in a string constant.
All constant text is escaped using the escape character '\'. When writing regular expressions, to get a single '\' from a string constant, use '\\' or to get a regexp string like '\s', use double escaping of backslash character like '\\s'. This is only true for text constants written in a formula—not when taken from an attribute value.
Constant strings within each other (for example, iterate(list, "prodval('single-back-slash: \\\\')")) require extra levels of escaping—one level of escaping per constant text.

For more information on regular expression, refer to the Regular Expression topic in Resource Materials online help here.

Regular Expression Function	Parameters	Description
REGEXPSEARCH	(searchtext, text [, pos])	Returns first character position in 'text', matching the regular expression 'searchtext'. Search will begin at character position 'pos'. Search is case sensitive. [pos] is optional, the default is 1. If not found, ‘N/A’ is returned.
REGEXPSEARCHI	(searchtext, text [, pos])	Same as function REGEXPSEARCH, but search is case insensitive. [pos] is optional, if not found ‘N/A’ is returned.
REGEXPSUBSTITUTE	(text, from, to)	Returns 'text' where all instances of text 'from' is replaced with text 'to'.

Regular Expression Function

Parameters

Description

REGEXPSEARCH

(searchtext, text [, pos])

Returns first character position in 'text', matching the regular expression 'searchtext'. Search will begin at character position 'pos'. Search is case sensitive.

[pos] is optional, the default is 1. If not found, ‘N/A’ is returned.

REGEXPSEARCHI

(searchtext, text [, pos])

Same as function REGEXPSEARCH, but search is case insensitive.

[pos] is optional, if not found ‘N/A’ is returned.

REGEXPSUBSTITUTE

(text, from, to)

Returns 'text' where all instances of text 'from' is replaced with text 'to'.

Representative Text Functions

These functions can be used to produce an alternative value for the defined text.

Representative Text Function	Parameters	Description
METAPHONE3	(text)	Returns the primary Metaphone 3 code. Metaphone 3 has two 'sounds like' strings for a given input string—a 'primary' and an 'alternate'. In most cases they are the same, but for non-English names they can be a bit different, depending on pronunciation. Metaphone 3, like Soundex, is based on the idea of constructing a representative code for an input string. Metaphone 3 is a good choice for person names. Metaphone 3 tries to give name variations or names that sounds like the same code.
METAPHONE3ALTERNATE	(text)	Returns the alternate Metaphone 3 code.
SOUNDEX	(text)	Return the four-character Soundex encoding of 'text'. The Soundex code for a name consists of a letter followed by three numerical digits: the letter is the first letter of the name, and the digits encode the remaining consonants. Similar-sounding consonants share the same digit. For example, the consonants B, F, P, and V are each encoded as the number 1. Vowels can affect the coding, but are not coded themselves except as the first letter. However, if 'h' or 'w' separate two consonants that have the same Soundex code, the consonant to the right of the vowel is not coded. The correct value can be found as follows: The first letter of the name is the letter of the Soundex code, and is not coded to a number. Replace consonants with digits as follows (after the first letter): b, f, p, v > 1 c, g, j, k, q, s, x, z > 2 d, t > 3 l > 4 m, n > 5 r > 6 h, w are not coded Two adjacent letters with the same number are coded as a single number. Letters with the same number, separated by an 'h' or 'w', are also coded as a single number. Continue until you have one letter and three numbers. If you run out of letters, fill in 0s until there are three numbers.

Representative Text Function

Parameters

Description

METAPHONE3

(text)

Returns the primary Metaphone 3 code. Metaphone 3 has two 'sounds like' strings for a given input string—a 'primary' and an 'alternate'. In most cases they are the same, but for non-English names they can be a bit different, depending on pronunciation.

Metaphone 3, like Soundex, is based on the idea of constructing a representative code for an input string. Metaphone 3 is a good choice for person names. Metaphone 3 tries to give name variations or names that sounds like the same code.

METAPHONE3ALTERNATE

(text)

Returns the alternate Metaphone 3 code.

SOUNDEX

(text)

Return the four-character Soundex encoding of 'text'.

The Soundex code for a name consists of a letter followed by three numerical digits: the letter is the first letter of the name, and the digits encode the remaining consonants. Similar-sounding consonants share the same digit. For example, the consonants B, F, P, and V are each encoded as the number 1. Vowels can affect the coding, but are not coded themselves except as the first letter. However, if 'h' or 'w' separate two consonants that have the same Soundex code, the consonant to the right of the vowel is not coded.

The correct value can be found as follows:

The first letter of the name is the letter of the Soundex code, and is not coded to a number.

Replace consonants with digits as follows (after the first letter):

b, f, p, v > 1

c, g, j, k, q, s, x, z > 2

d, t > 3

l > 4

m, n > 5

r > 6

h, w are not coded

Two adjacent letters with the same number are coded as a single number. Letters with the same number, separated by an 'h' or 'w', are also coded as a single number.

Continue until you have one letter and three numbers. If you run out of letters, fill in 0s until there are three numbers.

Test Functions

The functions can be used to perform a test and return a result.

Text Function	Parameters	Description
EXACT	(text1, text2)	Returns 1 (true) if 'text1' equals 'text2', otherwise returns 0 (false). The fields text1 and text2 can be straightforward text, such as 'Yes,' or can be the result of using another function, such as PRODVAL. It is common to use this function along with the IF function to compare an attribute value to a fixed text string. Refer to the example section below, and also the Conditional Functions topic here.
FIND	(searchtext, text [, pos])	Returns first character position in ‘text’ where ‘searchtext’ is found—search will begin at character position ‘pos’ (default is 1). Search is case sensitive. [pos] is optional. If not found, ‘N/A’ is returned.

Text Function

Parameters

Description

EXACT

(text1, text2)

Returns 1 (true) if 'text1' equals 'text2', otherwise returns 0 (false).

The fields text1 and text2 can be straightforward text, such as 'Yes,' or can be the result of using another function, such as PRODVAL. It is common to use this function along with the IF function to compare an attribute value to a fixed text string. Refer to the example section below, and also the Conditional Functions topic here.

FIND

(searchtext, text [, pos])

Returns first character position in ‘text’ where ‘searchtext’ is found—search will begin at character position ‘pos’ (default is 1). Search is case sensitive.

[pos] is optional. If not found, ‘N/A’ is returned.

Use Cases

For a scenario using the CONCATENATE and EXACT functions, refer to Comparing Localized Values Scenario here.

For a scenario using the CONCATENATE and EXACT functions, refer to Including Parent Info on Child Scenario here.

For a scenario using the CONCATENATE and EXACT functions, refer to Override Scenario here.