Text functions: Part 3

Overview

Text functions manipulate Text values and return other Text values.

Left

Extracts a substring from a string. Starting from the left-most character, returns the first amount characters of value.

Syntax

Left( value, amount )

The Left function syntax has the following arguments:

Examples

• Left ("Alphabet Soup", 5) returns "Alpha".

• Left ("Alphabet", 20) returns "Alphabet".

Length

Returns the length of value as an Integer.

Syntax

Length( value )

The required argument value may be of any type.

Example

Length( "Acme Software" ) returns 13.

LowerCase

Returns value converted to lowercase.

Syntax

LowerCase( value )

The required argument value may be of any type.

Remarks

Only uppercase letters are converted to lowercase; all lowercase letters and non-letter characters remain unchanged.

Example

LowerCase( "Black") returns "black".

PadLeft

Adds padding characters to left side of a string, making it specified length.

Syntax

PadLeft ( value, pad_character, length )

The PadLeft function syntax has the following arguments:

Example

PadLeft("bacon","m",9) returns "mmmmbacon" as a text string.

PadRight

Adds padding characters to right side of a string, making it specified length.

Syntax

PadRight ( value, pad_character, length )

The PadRight function syntax has the following arguments:

Example

PadRight("80302","0",9) returns "803020000" as a text string.

RandomText

Returns a text value of the specified length consisting of random nonsense words.

Syntax

RandomText( length )

The required argument length must be an integer between 0 and 10 million.

Remarks

This function produces a text value of length length consisting of random nonsense words of length 4-10 separated by spaces, taken from the letters a-z.  It is useful for creating synthetic test data.

Example

RandomText(20) might return "hompal eq neog haskc".

RandomText2

Returns a text value of the specified length consisting of nonsense words with identical letters.

Syntax

RandomText2( length )

The required argument length must be an integer between 0 and 10 million.

Remarks

Identical to RandomText except that the letters of each word are identical. This function is designed to produce more compressible results than RandomText.

Example

RandomText2(40) might return "qqqq lllllll dddddd wwwwwww vvvv ooooooo".

RemoveAccents

Returns the input text, with accented characters replaced by their unaccented equivalents.

Syntax

RemoveAccents( value )

The required argument value may be of any type.

Example

RemoveAccents("ÄāĈ") returns "AaC"

ReplaceAllSQLPat

Returns text, modified such that all occurrences of matching_pattern have been replaced with replacement.

Syntax

ReplaceAllSQLPat( text, matching_pattern, replacement )

The ReplaceAllSQLPat function syntax has the following arguments:

Remarks

Use this function only if you want to use SQL-like patterns in the matching string. To replace plain text, use ReplaceAllText.

The matching_pattern argument is similar to SQL, and may contain the following specially-interpreted characters:

• A matching pattern containing "_" will match any character in that position. For example, "_on" will match both "Jon" and "Don".

• A matching pattern containing "%" will match any sequence of zero or more characters in that position. For example, "%cost%" will match any string containing the substring "cost".

• A matching pattern containing "@" will cause a following "%" or "_" to be interpreted as a literal character instead of a matching pattern. Use this to match special characters. For example "%@%%" will match any string containing a "%" character.

The replacement argument may any string expression, including literal strings like "john", and field values like NAME (where NAME is a field in the record). If replacement is not a literal string it is re-evaluated for each record before performing the substitution.

Examples

• ReplaceAllSQLPat( "(806)-747-9944" , "(%)", "" ) returns "-747-9944".

• ReplaceAllSQLPat( " Don Fred Jon" , "_on", "XXX" ) returns "XXX Fred XXX".

ReplaceAllText

Returns value, modified such that all occurrences of matching_string have been replaced with replacement.

Syntax

ReplaceAll( value, matching_string , replacement )

The ReplaceAll function syntax has the following arguments:

Remarks

The matching_string and replacement arguments may be any text expressions including literal strings like "john", and field values like NAME.

Examples

• ReplaceAllText( "duck, duck, goose", "duck", "pig" ) returns "pig, pig, goose".

• ReplaceAllText( "806-747-9944" , "-", "" ) returns "8067479944".

• ReplaceAllText( FULLNAME, LASTNAME, "") removes any occurrences of LASTNAME from FULLNAME.

• ReplaceAllText( ADDRESS, Chr(9), "") removes all tab characters from ADDRESS.

ReplaceFirstSQLPat

Returns text, modified such that the first occurrence of matching_pattern has been replaced with replacement.

Syntax

ReplaceFirstSQLPat( text, matching_pattern, replacement )

The ReplaceFirstSQLPat function syntax has the following arguments:

Remarks

Use this function only if you want to use SQL-like patterns in the matching string. To replace plain text, use ReplaceFirstText.

The matching_pattern argument is similar to SQL, and may contain the following specially-interpreted characters:

• A matching pattern containing "_" will match any character in that position. For example, "_on" will match both "Jon" and "Don".

• A matching pattern containing "%" will match any sequence of zero or more characters in that position. For example, "%cost%" will match any string containing the substring "cost".

• A matching pattern containing "@" will cause a following "%" or "_" to be interpreted as a literal character instead of a matching string. Use this to match special characters. For example "%@%%" will match any string containing a "%" character.

The replacement argument may any string expression, including literal strings like "john", and field values like NAME (where NAME is a field in the record). If replacement is not a literal string it is re-evaluated for each record before performing the substitution.

Examples

• ReplaceFirstSQLPat( "(806)-747-9944" , "(%)", "" ) returns "-747-9944".

• ReplaceFirstSQLPat( " Don Fred Jon" , "_on", "XXX" ) returns "Don Fred XXX".

ReplaceFirstText

Returns value, modified such that the first occurrence of matching_string has been replaced with replacement.

Syntax

ReplaceFirstText( value, matching_string, replacement )

The ReplaceFirstText function syntax has the following arguments:

Remarks

The matching_string and replacement arguments may be any text expressions including literal strings like "john", and field values like NAME.

Examples

• ReplaceFirstText( "duck, duck, goose", "duck", "pig" ) returns "pig, duck, goose".

• ReplaceFirstText( "806-747-9944" , "-", "" ) returns "806747-9944".

• ReplaceFirstText( FULLNAME, LASTNAME, "") removes the first occurence of LASTNAME from FULLNAME.

• ReplaceFirstText( ADDRESS, Chr(9), "") removes the first tab character from ADDRESS.

Reverse

Returns text with character positions reversed.

Syntax

Reverse( text )

The required argument text must be of type Text.

Example

Reverse("erehwoN") returns "Nowhere".