Function

Description

Syntax

Examples

ASC

Returns the numeric code for the character char, according to Unicode encoding.

ASC(char)

ASC(‘A’)
Returns the Unicode equivalent of the character; in this case: 65

AT

Returns the character at the position indicated by the number n. If n is out of range, an empty string is returned.

AT(text,pos)
where pos is a numeral indicating the character position from the start of the string

AT(Middletown,6)
Returns the sixth character of the text string: e

BEGINS

Checks (true or false) whether the string begins with the designated text string (pattern). The test is case-insensitive.

(text,pattern)
where pattern is the text to be matched against the text of the same length at the beginning of the string

BEGINS(‘hello world’, ‘world’)
Indicates whether the string begins with the string “world”; in this case, returns false

BEGINS(‘hello world’, ‘hello)
This expression would return true

CAPITAL

Converts the string to sentence case (first word capitalized).

CAPITAL(text)

CAPITAL(@COMP_NAME)
Displays the string in sentence case; for example: Atlas city.

CAPITALW

Converts the string to title case (each main word capitalized and separated by underscores or white spaces).

CAPITALW(text)

CAPITALW(@COMP_NAME)
Displays the string in title case; for example: Atlas City

CHR

Returns the character represented by the given Unicode code.

CHR(code)

CHR(‘65’)
Returns the character represented by the Unicode number (code), in this case:
A

COMPACT

Compacts the given text by replacing consecutive spaces with a single space, and removing leading or trailing spaces.

COMPACT(text)

COMPACT('  hello    world   ') == 'hello world'
Removes spaces at the beginning and ends of the string, and replaces all groups of spaces with a single spaces; in this case, the result would be:
‘hello world’

CONTAINS

Checks (true or false) whether the string contains the designated text string (pattern). The test is case-insensitive.

CONTAINS(text,
pattern)
where pattern is the text to be matched against the contents of the string

CONTAINS(‘hello world’, ‘wor’)
Indicates whether the string contains the text “wor”; in this case, returns true

CONTAINS(‘hello world’, ‘hall)
This expression would return false

ENDS

Checks (true or false) whether the string ends with the designated text string (pattern). The test is case-insensitive.

(text,pattern)
where pattern is the text to be matched against the text of the same length at the end of the string

ENDS(‘hello world’, ‘world’)
Indicates whether the string ends with the string “world”; in this case, returns true

ENDS(‘hello world’, ‘hello’)
This expression would return false

FILL

Fills a string of a given length with the specified (“pad”) characters.

FILL(len,pad)
where len is the desired number of characters in the string and pad is the character or characters for filling

FILL(8,*)
Returns a string of eight characters, using * to represent all empty characters in the string. For example, HOME would be displayed as HOME****

LEFT/
RIGHT

Returns a substring of a specified number of characters, starting from the end of the string (RIGHT) or beginning of the string (LEFT).

LEFT(text,len)
RIGHT(text,len)

LEFT(@@,3)
Displays the first three characters of the returned string. For example, DOCUMENTATION would be displayed as DOC

RIGHT(@SHORT_TEXT,4)
Displays the last four characters of the string. For example, a value of FINALCOST would be displayed as COST

LEN

Returns the number of characters in the string.

LEN(text)

LEN(‘hello world’)
Returns the number of characters in the text string; in this case, 11

LIKE

Checks (true or false) whether the designated search string (pattern) matches the text string. The test is case-insensitive.

(text,pattern)
where pattern is the search text, to be matched against the text of string; it may contain wildcard characters

LIKE(‘hello world’, ‘war’)
Indicates whether the string matches the search string “*world”; in this case, returns true

LIKE(‘hello world’, ‘*war’)
This expression would return false

LOWER/
UPPER

Converts the returned string to lower case or upper case, respectively.

LOWER(text)
UPPER(text)

LOWER(@COUNTRY_NAME)
Displays the string in all lower-case letters; for example: usa

UPPER(@@)
Displays the string in all capital letters; for example: ATLAS

LPAD/
RPAD/
ZPAD

Pads a string on the left/right (respectively) until the specified length of characters is reached.
ZPAD pads strings on the left with zeroes to reach a specified length, but only if text is an integer.

LPAD(text,len,
pad)
where len is the desired number of characters in the string and pad is the character or characters for padding

LPAD(‘hello’,8,‘*’)
Adds the number of specified characters (in this case an asterisk) needed to reach the specified number of characters (8 in this example). In this case, the following would be displayed: ***hello

RPD(‘hello’,4,‘*’)
would return: hell

ZPD(‘1000’,10)
would return: 0000001000
but ZPD(‘hello’,10) would return: hello

MID

Returns a string starting a specified number of characters from the beginning of the string and containing the next specified number of characters.

The counting of characters begins from 0.

MID(text,start,len)
where start is the character number indicating the position at which to begin returning the characters, and len is the number of characters to display in the returned string

MID(@SHORT_TEXT,5,3)
Displays a substring consisting of the sixth, seventh and eighth character of the string. For example, DOCUMENTATION would be displayed as ENT

PREF

Returns the leftmost n characters in the text string, converting them to upper case.

PREF(text,len)
where len is the number of characters to return, starting from the first (leftmost) character in the string

PREF(‘hello world’,5)
Counts the indicated number of characters from the start of the string and converts them to upper case. In this example, the following would be displayed: HELLO

REPLACE

Replaces all occurrences of the given string (pattern) with the designated replacement string. The Replace operation is case-sensitive.

REPLACE(text,pattern,repstr)
where pattern is the search text and repstr is the text to replace each occurrence of the pattern

REPLACE(‘abcabc’,’b’,’***’)
Searches for each occurrence of the search pattern (in this case b) and replaces it with the search string (in this case ***). In this example, the following string would be returned:
a***ca***c

REPLACE(‘abcabc’,’bc’,’’)
Would return: aa

TRANSLATE

Indicates the string parts that require translation.

TRANSLATE(text,
[tType])
where text is the string to be translated and tType is the (optional) type classification of the string. See the Text Type Classifications table for the available classifications.

=IF(@Status=="1", TRANSLATE("green"), TRANSLATE("red"))
Specifies that the values green and red should be included in the translation file. If these values are translated (using translation tools), the corresponding translated text appears at runtime, according to the value of the Status field.

=(TRANSLATE("Football", "XTIT")=="Football")
When used in the Hidden property of a control, hides the control for languages that translate the term 'football' as something other than 'football'. XTIT is the Title string classification.

Do not use TRANSLATE for static values (use it only for dynamic expressions) because interpretation of the TRANSLATE function may affect runtime performance.

TRIM/
LTRIM/
RTRIM

Removes the white space from both ends of the string.
LTRIM removes the white space only from the left and RTRIM removes all the trailing spaces.

TRIM(text)
LTRIM(text)
RTRIM(text)

TRIM(@YEAR_TO_END)
Removes the white space in the returned value. For example, the value “    ANNUAL TURNOVER    “ would be displayed as ANNUAL TURNOVER (without the spaces at the beginning and the end).

LTRIM(‘   hello world  ‘)
Removes white space only from the left; in this case, the following would be displayed: hello world 

& (Concatenate using Logical AND)

Connects two returned values in order to produce a single, continuous value. NOTE that only two arguments can be concatenated.

(string)&(string)

(@CITY_NAME)&(STATE_NAME)
Displays the returned values of the two fields as a single value. For example, if the values DAYTON and OHIO are returned for the two fields, the value DAYTON OHIO would be displayed.