Functions

                  

Expand/Collapse all Show/Hide All

Functions take one or more arguments and return either a character string or a numeric value. The resulting value of a function can, in turn, be used to perform string operations, numeric calculations, or used as arguments for another function.

 

Expand/Collapse item  ABS - Absolute Value Function

Format

ABS (numexpr)

Description

The ABS (numexpr) function is a numeric value that returns the absolute value of the numeric expression provided. The absolute value is the positive value of the provided number.

Example

ABS (-100) returns 100

ABS (10-100) 90

ABS (NUM(ML_CREDIT$)) returns the absolute value of the numeric data contained in panel control ML_CREDIT

 

Expand or collapse item ASC - ASCII Function

Format

ASC ( strexpr )

Description

The ASC (strexpr) function is a string value that returns a numeric value. The ASCII value of the character is returned specified by the string expression. Only the first character in the string is converted.

Example

ASC ("A") returns numeric value 65

ASC ("abc") returns numeric value 97

 

Expand/Collapse item  CHR - Character Conversion

Format

CHR ( numexpr )

Description

The CHR function is a numeric value that returns a single-character string value containing the ASCII character whose decimal value is defined by numexpr.

Example

CHR (65) returns character "A"

CHR (33) returns character "!"

 

Expand/Collapse item  DTE - Date/Time Function

Format

DTE ( numexpr1 [,numexpr2] [:strexpr] )

Where

numexpr1 is a numeric value containing the Julian date to convert (if zero, the current date is used) that returns a string value

 

numexpr2 is an optional time value containing hours and fractions of hours past midnight (if not present, the current time is used) that returns a string value

 

strexpr is a string describing the format of the date to return (if no format is specified, the format of MM/DD/YY is used) that returns a string value

Description

The DTE function converts a date (and time) from Julian form to a string. The strexpr defines the format to return in which the various components of the date are represented by a percent sign (%) followed by a one- or two-letter code. The first letter indicates a source for the data (day, month, year, etc.); the second letter, if specified, indicates the string format.

 

To use the DTE function based on data from a panel control, you must first calculate the Julian date using the JUL function.

 

The following table illustrates the valid codes for the date and time mask.

Code

Source

Default

Long(L)

Short(S)

Zero(Z)

%W

Day of week

1-7

Monday-Sunday

Mon-Sun

01-07

%D

Day of month

1-31

Monday-Sunday

Mon-Sun

01-31

%M

Month in year

1-12

January-December

Jan-Dec

01-12

%Y

Year

1970-1999

1970-1999

1970-1999

70-99

%H

Hour

0-24

0-24

0-24

00-24

%J

Day in year

1-365

1-365

1-365

01-99

%m

Minute

0-59

0-59

0-59

00-59

%s

Second

0-59

0-59

0-59

00-59

%p

Am/Pm

am,pm

am,pm

am,pm

am,pm

A code followed by "l" (for example, %Wl) indicates a long text format, as indicated in the Long(l) column above. A code followed by "s" (for example, %Ws) indicates a short text format, as indicated in the Short(s) column. A code followed by "z" indicates the value is to be converted to a two-digit, zero-filled value, as indicated in the Zero(z) column.

Any other characters (for example, "/" or "-") in the date format mask are copied as is to the output. To output a %, you must use %%.

Note The mask must be enclosed in quotation marks. For example, DTE (0:"%Dl %Ml %D/%Y %Hz:%mz %p") uses the current date and time to output the following:

Monday May 31/2010 10:27 pm

 

Expand or collapse item  FPT - Fractional Part Function

Format

FPT ( numexpr )

Description

The FPT function is a numeric value that returns the fractional portion of the numeric value specified.

Example

FPT (1.345) returns .345

FPT (105.95) returns .95

FPT (NUM(ML_CREDIT$)) returns the fractional portion of the numeric value contained in the ML_CREDIT panel control

 

Expand or collapse item  INT - Integer Function

Format

INT ( numexpr )

Description

The INT function is a numeric value that returns the integer portion of the value specified. No rounding is performed on the value. The fractional part of the value is dropped.

Example

INT (3.23) returns 3

INT (-5.6) returns -5

INT (.9999) returns 0

INT (NUM(ML_CREDIT$)) returns the integer value of the data contained in the panel control ML_CREDIT

 

Expand or collapse item  JUL - Julian Function

Format

JUL ( numexpr1, numexpr2, numexpr3 )

Description

The JUL function returns a numeric value and is used to convert a date from year, month, and day to a Julian date. Julian dates are useful for calculating the number of days separating two dates.

numexpr1 contains the numeric value of the year (if zero, the current year is used)

numexpr2 contains the numeric value of the month (if zero, the current month is used)

numexpr3 contains the numeric value of the day within the month (if zero, the current day is used)

Example

If the date stored in ML_DT$ contains the string "01/15/1998", the following expression:

JUL(NUM(ML_DT$(7,4)),NUM(ML_DT$(1,2)),NUM(ML_DT$(4,2))) returns the value 2450829

 

Expand/Collapse item  LCS - Lowercase Function

Format

LCS ( strexpr )

Description

The LCS function is a string value that returns a string composed of the original string with all uppercase alphabetic characters replaced with their corresponding lowercase value.

Example

LCS ("JOHN SMITH") returns "john smith"

LCS (ML_NAME$) returns the string value of panel control ML_NAME all in lowercase values

 

Expand/Collapse item  LEN - Length Function

Format

LEN ( strexpr )

Description

The LEN function is a string value that returns the length of a string value. If the string provided is a null string (""), a length of zero is returned.

Example

LEN ("Hello") returns 5
LEN ("") returns 0
LEN ("A"+"BC") returns 3

LEN (ML_NAME$) returns the length of the string value in panel control ML_NAME

 

Expand/Collapse item  MAX - Maximum Value Function

Format

MAX ( numexpr1, numexpr2,& )

Description

The MAX function returns the maximum value of the numeric values or expression specified. There is no limit to the number of values or expressions.  

 

numexpr1, numexpr2 are numeric values

Example

MAX (10, 5, 8) returns number 10

MAX (1000,NUM(ML_CREDIT$)) returns either 1000 or the numeric value of panel control ML_CREDIT, whichever is greater

 

Expand/Collapse item  MID- Extract a Portion of a String

Format

MID ( strexpr, numexpr1, numexpr2 )

Description

The MID function is a string value that returns a portion of a string. The returned value comprises the characters beginning at position numexpr1 in the string, extending for the number of characters specified by numexpr2. Unlike using substring references, the MID function does not produce an error if a value is specified that does not exist within the string.

 

numexpr1, numexpr2 are numeric values

Example

MID ("John Smith", 6, 5) returns "Smith"

MID ("John Smith", 1, 30) returns "John Smith"

MID ("John Smith", 30, 30) returns ""

 

Expand/Collapse item  MIN - Minimum Value Function

Format

MIN ( numexpr1, numexpr2,& )

Description

The MIN function returns the minimum value of the numeric values or expression specified. There is no limit to the number of values or expressions. numexpr1, numexpr2 are numeric values.

Example

MIN (10, 5, 8) returns number 5

MIN (1000,NUM(ML_CREDIT$)) returns either 1000 or the numeric value of panel control ML_CREDIT, whichever is smaller

 

Expand/Collapse item  MOD - Modulo Function

Format

MOD ( numexpr1, numexpr2 )

Description

The MOD function returns the remainder from a division of the first expression by the second. The number returned is always an integer value. numexpr1, numexpr2 are numeric values.

Example

MOD (10, 3) returns 1

MOD (10, 5) returns 0

MOD (9, 3.5) returns 2

 

Expand/Collapse item  NUM Function

Format

NUM ( strexpr )

Description

The NUM function is a string value that returns the numeric value of strexpr. If the string does not contain a valid numeric value, a 0 (zero) is returned.

The string expression can contain any combination of the characters 0-9, space, comma, equals sign, or dollar sign. One decimal point can be included along with one sign character (either "-" or "+").

Example

NUM ("123") returns 123

NUM ("-1,005.00") returns -1005

NUM ("ABC") returns 0

NUM (ML_CREDIT$) returns the numeric value of the data in the panel control ML_CREDIT

 

Because most variables are string variables, you must first convert them to a numeric value before performing a mathematical operation. The NUM function is available to convert the string variables to a numeric value.

The format of the NUM function is NUM(strvar$), where strvar$ is the string variable.

Example To multiply the commission amount in ML_COMMISSION by the commission rate in ML_COMM_RATE, use the following expression:

 [NUM(ML_COMMISSION$) * NUM(ML_COMM_RATE$) * .01]

Note The .01 is used to multiply the resulting number because the rate is a percentage amount.

Although the data references are expected to return a text value, it is not necessary to reconvert numeric values back to string values using the STR function. If the resulting value is a number, the number is converted into a string form automatically.

 

Expand/Collapse item  PAD Function

Format

PAD ( strexpr1, numexpr1 [ ,numexpr2 [ ,strexpr2] ] )

Description

strexpr1 is a string value that returns a string value

numexpr1 is the desired length of the returned string

numexpr2 is an optional parameter that defines how to pad the string:

0 = pad on left (right justify)
1 = pad on right (left justify)
2 = center in string

The first character of strexpr2 is used to pad the strexpr1 (if omitted, blanks are used).

The PAD function returns a character string of the length specified (numexpr1) by either truncating the strexpr or by appending the defined pad character (strexpr2); spaces are used if strexpr2 is omitted. If numexpr2 is omitted, the pad on right (left justify) option is used.

Example

PAD ("ABC",5) returns "ABC  ", a 5-character string with two trailing spaces

PAD ("ABC",5,0) returns "  ABC", a 5-character string with two leading spaces

PAD ("ABCDE",3) returns "ABC", a 3-character string

PAD ("ABC",5,"X") returns "ABCXX"

 

Expand/Collapse item  PRC Function

Format

PRC ( numexpr1, numexpr2 )

Description

numexpr1 is a numeric value to be rounded

numexpr2 is the precision at which to round the numeric value

The PRC function returns the rounded value of the numeric value supplied in numexpr1. The second parameter represents the desired precision at which to round. The precision determines the number of decimal places that will be returned. The precision must be in the range of 0 through 14.

Example

PRC (1.3456, 2) returns 1.35

PRC (1.3456, 3) returns 1.346

PRC (1.567, 0) returns 2

 

Expand/Collapse item  STP- Strip Function

Format

STP ( strexpr, numexpr [,strexpr2] )

Description

strexpr is a string value that returns a string value.

numexpr is an optional numeric code value that defines how to strip the string (if omitted, trailing characters are stripped)

0 - strip leading characters
1 - strip trailing characters
2 - strip both leading and trailing
3 - strip all characters

strexpr2 is the optional character to be stripped (if omitted, blanks are stripped)

The STP function returns a character string generated by stripping the first character of strexpr2 (spaces if omitted) from the value of strexpr1. The data is stripped from the front, the end, or from both the front and end of strexpr1, depending on the setting of numexpr.

Example

STP ("  ABC",0) returns "ABC"

STP ("ABC  ",1) returns "ABC"

STP ("**TEST**,2,"*") returns "TEST"

STP (" A B C ",3) returns "ABC"

 

Expand/Collapse item  STR - String Function (Convert Numeric)

Format

STR ( numexpr [:strexpr] )

Description

numexpr is a numeric value that returns a string value.

strexpr is the format mask to be used in the conversion process.

The STR function converts the numeric value (or expression) to a character string. A mask string can be used to specify the size and format of the resulting character string.

Example

STR (100) returns string "100"

STR (5 * 6) returns string "30"

STR (5 * 6:"0000") returns string "0030"

STR (NUM(ML_CREDIT$)*.1) returns a string value of the amount stored in panel control ML_CREDIT multiplied by 0.10

 

Expand/Collapse item  STR - String Function (Convert String)

Format

STR ( strexpr1 : strexpr2 )

Description

strexpr1 is a string value.

strexpr2 is the format mask to be used in the conversion process.

This is a second form of the STR function to use on string values. The STR function converts the string value (or expression) based on the contents of a format mask. This function is particularly useful for formatting data such as phone numbers and Social Security numbers.

Example

STR ("cat" : "AAA") returns string "CAT"

STR ("MIKE" : "Aa(10)") returns "Mike      "

STR ("1234567":"000-0000") returns "123-4567"

 

Expand/Collapse item  UCS - Uppercase Function

Format

UCS ( strexpr )

Description

strexpr is a string value that returns a string value. The UCS function returns a string composed of the original string with all lowercase alphabetic characters replaced with their corresponding uppercase value.

Example

UCS ("John Smith") returns "JOHN SMITH"

UCS (ML_NAME$) returns the string value of panel control ML_NAME all in uppercase

© Sage Group plc 2019. Privacy Policy | Copyright/Trademarks