# CHAPTER 10: Functions

## 10.1 Introduction

Empress functions can be divided into Aggregate Functions, Build-In Functions, Math Library Functions, User Defined Functions and User Defined Aggregate Functions.

The Aggregate Functions are `COUNT`, `MAX`, `MIN`, `SUM`, `AVG`.

The Build-In Functions are listed below:

abs ( ) ceiling ( ) datenext ( ) floor ( ) is_decimal ( ) is_integer ( ) is_white ( ) | length ( ) lpad ( ) lscan ( ) ltrim ( ) nullval ( ) round ( ) | rpad ( ) rscan ( ) rstrindex ( ) rtrim ( ) sign ( ) strdel ( ) | strindex ( ) strins ( ) substr ( ) tolower ( ) toupper ( ) trunc ( )

The Math Library Functions are vary with the system being used. They will be a subset of the following:

acos asin atan atan2 ceil cos cosh erf erfc | expr fabs floor fmod frexp frexpman frexpexp gamma hypot | j0 j1 jn jdexp log log10 modf modffrac modfint | pow sin sinh sqrt tan tanh y0 y1 yn

The User Defined Functions and User Defined Aggregrate Functions are functions defined in accordance with the details specified in the Empress: User Defined Functions manual.

## 10.2 Aggregate Functions

There are five functions which may be applied to groups of records in a `SELECT` command or expression: `COUNT`, `MAX`, `MIN`, `SUM` and `AVG`. These functions are described in the following subsections.

### 10.2.1 COUNT Function

Using the `COUNT` function in a `SELECT` or expression gives the number of records in each group. If there is no `GROUP BY` clause in the command, all the records selected form a group and the function returns the number of records selected.

The `COUNT` function may appear in the list of items to be selected and in the `HAVING` clause of the command.

Syntax

  `COUNT [` | `|(*) |expr |(DISTINCT |` | `|attr |expr` | `|) |` | `|] | | |`

Note

  If `DISTINCT` or `UNIQUE` is included, duplicate values are eliminated. Note that `DISTINCT` may only be used once to qualify a list of selected items. If `DISTINCT` is applied outside the `COUNT` function, the `COUNT` does not exclude duplicate records.

Example

1. Simple Example

   The total number of loans outstanding is counted by the command:

          SELECT COUNT FROM loans;

   which produces:

          COUNT (*)

          12

   or

          SELECT COUNT (*) FROM loans;

   which produces the same result. Note that the function is used as the column heading.

2. Count the Unique Number of Attribute Values

   The number of employees with loans is counted with the command:

          SELECT COUNT (DISTINCT name) FROM loans;

   producing:

          COUNT (DISTINCT name)

          6

3. Count the Number of Attribute Values with Condition

   To find out how many records in the `loans` table are outstanding to Mosca, use:

          SELECT COUNT FROM loans WHERE name = 'Mosca';

   producing:

          COUNT (*)

          2

   To find out how many records in the `personnel` table have a phone number beginning with `961`, use:

          SELECT COUNT FROM personnel
             WHERE phone match "961*";

   producing:

          COUNT (*)

          1

4. Use Functions with Other Selected Items

   The `COUNT` function can be used when other items are selected, as in:

          SELECT name, amount, COUNT FROM loans;

   It produces:

          name          amount

          Mosca         $150.00
          Jones          $33.95
          Kilroy        $250.00
          Wladislaw      $55.00
          Jones          $25.00
          Mosca         $200.00
          Wladislaw      $25.00
          Peterson       $50.00
          Wladislaw      $75.00
          Jones         $300.00
          Scarlatti     $150.00
          Scarlatti     $125.00

          COUNT (*) = 12

### 10.2.2 AVG, MAX, MIN and SUM Functions

The `AVG`, `MAX`, `MIN` and `SUM` functions are applied to the values of the specified attribute over the group. These functions produce a single value as follows:

  `SUM` | Sum of the values in the group.
  `AVG` | Average of the values in the group.
  `MAX` | Largest value in the group.
  `MIN` | Smallest value in the group.

Syntax

  `|AVG |MAX |MIN |SUM` | `| ([DISTINCT] attr) | | |`

Note

1. The functions `SUM` and `AVG` apply only to numeric attributes. If `UNIQUE` or `DISTINCT` is specified, duplicate values are excluded.

2. Note that there is no point in specifying `DISTINCT` with `MAX` or `MIN`, since it does not have any logical effect.

Example

1. Example of MAX Function

   To find the largest loan in the `loans` table:

          SELECT MAX (amount) FROM loans;

   the result:

          MAX (amount)

          $300.00

2. Example of MIN Function

   To find the date of the oldest loan, use:

          SELECT MIN (date) FROM loans;

   the result:

          MIN (date)

          2 February 1990

3. Example of MAX Function with Condition

   To find the highest credit limit of all the personnel whose names start with the letter `A` to `M`, use:

          SELECT MAX (credit_limit) FROM personnel
             WHERE name < 'N';

   the result:

          MAX (credit_limit)

          $750.00

4. Example of AVG Function

   To find the average of all the amounts in the `loans` table, use:

          SELECT AVG (amount) FROM loans;

   the result:

          AVG (amount)

          119.91

5. Example of SUM Function with Condition

   To find the sum of all the loans outstanding to Mosca, use:

          SELECT SUM (amount) FROM loans
             WHERE name = 'Mosca';

   the result:

          SUM (amount)

          350.00

6. Functions Used In Combination

   The functions may be used in combination:

          SELECT COUNT, SUM (amount), AVG (amount), MAX (amount),
             MIN (amount) FROM loans;

   the result:

          COUNT(*)  SUM (amount)  AVG (amount)  MAX (amount)  MIN(amount)

          12        1438.95       119.91        $300.00       $25.00

### 10.2.3 Suppressing Duplicates

Duplicates may be produced in a selected list of attribute values by default, or by using the keyword `ALL`. Although the result is consistent with the underlying database operations, it is not always desirable. The command:

       SELECT name FROM loans;

will produce a list of names with 12 entries, in which Mosca and Scarlatti appear twice, and Jones and Wladislaw three times each. To overcome this, the keyword `DISTINCT` is used in the `SELECT` command to produce a list of attribute values with no duplicates. `UNIQUE` may be used as a synonym for `DISTINCT`.

The keyword `DISTINCT` may precede the argument of the functions `SUM`, `AVG`, and `COUNT`.

For example, the following command produces a list of names with no duplicates:

        SELECT DISTINCT name FROM loans;

the result:

       name

       Mosca
       Jones
       Kilroy
       Wladislaw
       Peterson
       Scarlatti

To find out how many different employees have loans, use:

       SELECT COUNT (DISTINCT name) FROM loans;

the result:

       COUNT (DISTINCT name)

       6

## 10.3 Build-In Functions

The following is a list of Empress Build-In Functions and their brief descriptions:

Table 10-1: Build-In Functions

Build-in Functions | Description
`abs ( )` | Returns the absolute value of a given numeric data.
`ceiling ( )` | Returns a value rounded up to a specified number of decimal digits.
`datenext ( )` | Returns the next date after a specified date.
`floor ( )` | Returns a given value truncated to a specified number of decimal digits.
`is_decimal ( )` | Returns true for a given character string which can be converted to an Empress decimal value without the loss of precision.
`is_integer ( )` | Returns true for a given character string which can be converted into an Empress integer without loss of precision.
`is_white ( )` | Returns true when a given string contains only space, tab, new line, carriage return, vertical tab or form feed.
`length ( )` | Returns the length of a given string.
`lpad ( )` | Returns a string left-padded by a specified string with a given length.
`lscan ( )` | Returns the position of the nth occurrence of a given string.
`ltrim ( )` | Trims a specified character string from the left side of a given character string.
`nullval ( )` | Returns the default value of an attribute with a null value. If the attribute value is not null, then nullvall ( ) returns its value.
`round ( )` | Returns a number rounded to the number of specified decimal digits.
`rpad ( )` | Returns a string right-padded by a specified string with a given length.
`rscan ( )` | Returns the starting position of the nth occurrence of the specified string.
`rstrindex ( )` | Returns the starting position of the nth occurrence of the specified string.
`rtrim ( )` | Trims a specified character string from the right side of a given character string.
`sign ( )` | Returns a value to indicate whether a given numeric value is positive, negative or zero.
`strdel ( )` | Returns a given string with characters deleted according to the specified position and optional length argument.
`strindex ( )` | Returns the position of the nth occurrence of a given string.
`strins ( )` | Returns a string with another string inserted in front of a specific character position.
`substr ( )` | Returns a substring of of a given string, starting at specified character position and ending at the another character position.
`tolower ( )` | Returns a given string with all alphabetic characters changed to lowercase.
`toupper ( )` | Returns given string with all alphabetic characters changed to uppercase.
`trunc ( )` | Returns a number truncated to a specified number of digits following the decimal point.

The detail descriptions and the examples of Empress Build-In Functions are described below:

### 10.3.1 abs ( )

`abs ( )` returns the absolute value of a given numeric data.

Syntax

      abs (number)

  where:

  `number` | is a numeric value or numeric attribute. It can also be a result of a numeric expression.

Notes

  `abs ( )` will not handle null attributes.

Example

  The following yields a value of `750`.

         abs (750-1500)

  The following yields an absolute value of attribute `amount`.

         SELECT abs (amount) FROM loans;

### 10.3.2 ceiling ( )

`ceiling ( )`returns a value rounded up to a specified number of decimal digits.

Syntax

      ceiling (decimal [, digits])

  where:

  `decimal` | is a numeric value, a numeric attribute or a result of a numeric expression.
  `digits` | is the number of digits after the decimal place. It may take on both positive and negative values and the default is zero digits to the right of the decimal point.

Notes

  `ceiling ( )` will not handle null values.

Example

         ceiling (12.345)

  Yields a value of `13.0`.

         ceiling (12.345, 2)

  Yields a value of `12.35`.

         ceiling (12.345, -1)

  Yields a value of `20.0`.

### 10.3.3 datenext ( )

`datenext ( )` returns the next date after a specified date.

Syntax

  `datenext (date,` | `|day_name |day_number` | `|) |`

  where:

  `date` | is a date string or a `DATE` or `TIME` data type attribute.
  `day_name` | is the day of the week, i.e., Monday, Tuesday, etc.
  `day_number` | is the number of the day of the week, i.e., 1 is Monday, 2 is Tuesday, etc.

Notes

1. Day numbering begins with Sunday as day 0.

2. `datenext ( )` will not handle null values.

3. Keyword `TODAY` and `NOW` can be use as date value.

Example

  If today is taken as Monday, February 2, 1998, then:

         datenext ("TODAY", "Monday")

  Returns a value of `February 9, 1998` in a generic format and `19980209000000` in a `TIME` format.

  If today is taken as Monday, February 2, 1998, then:

         datenext ("TODAY", 1)

  Returns a value of `February 9, 1998`.

  If today is taken as Monday, February 2, 1998, then:

      datenext ("TODAY", 2)

  Returns a value of `February 3, 1998`.

### 10.3.4 floor ( )

`floor ( )` returns a given value truncated to a specified number of decimal digits.

Syntax

      floor (number [, digits])

  where:

  `number` | is a numeric value or any numeric data type attribute.
  `digit` | is the number of digits after the decimal place. It may take on both positive and negative values and the default is zero digits to the right of the decimal point.

Notes

1. `DOLLAR` data is converted to `FLOAT`.

2. `floor ( )` will not handle null values.

Example

         floor (12.345)

  Yields a value of `12.0`.

         floor (12.345, 2)

  Yields a value of `12.34`.

         floor (12.345, -1)

  This yields a value of `10.0`.

### 10.3.5 is_decimal ( )

`is_decimal ( )` returns true for a given character string which can be converted to an Empress decimal value without the loss of precision.

Syntax

      is_decimal (string [, max_digits [, decimal_places]])

  where:

  `string` | is any character string, `CHARACTER` or `TEXT` data type attribute.
  `max_digits` | is the maximum number of digits allowed for the converted value. It defaults to 15.
  `decimal_places` | is the number of decimal places allowed for the converted value. It defaults to the number of decimal places in the source string to a maximum of 15.

Notes

1. `is_decimal ( )` will not handle null values.

2. `is_decimal ( )` function is used in the `WHERE` clause.

Example

         is_decimal ("12.345", 5, 3)

  Returns a value of `true`.

         is_decimal ("12.3450", 5, 3)

  Returns a value of `true`.

         is_decimal ("12.3456", 5, 3)

  Returns a value of `false`.

         SELECT FROM loans WHERE is_decimal (name);

  No record is selected.

### 10.3.6 is_integer ( )

`is_integer ( )` returns true for a given character string which can be converted into an Empress integer without loss of precision.

Syntax

      is_integer (string [, nbytes])

  where:

  `string` | is any character string, `CHARACTER` or `TEXT` data type attribute.
  `nbytes` | is the number of bytes. The default is 4.

Notes

1. When `nbytes` is specified, the result is true only when the converted value will fit in an integer with the specified number of bytes. `nbytes` may take on the values between 1 and 4. If `nbytes` is greater than 4, it returns a value of false.

2. `is_integer ( )` will not handle null values.

3. `is_integer ( )` is used in the `WHERE` clause.

Example

  Given table `T1` having a four-character attribute `ATTR1` and three records, for example, `abcd`, `123`, `1234`.

         SELECT FROM T1 WHERE is_integer (ATTR1);

  Returns `123` and `1234`.

         SELECT FROM T1 WHERE is_integer (ATTR1, 1);

  Returns `123`.

### 10.3.7 is_white ( )

`is_white ( )` returns true when a given string contains only space, tab, new line, carriage return, vertical tab or form feed.

Syntax

      is_white (string)

  where:

  `string` | is any character string, `CHARACTER` or `TEXT` data type attribute.

Notes

1. `is_white ( )` will not handle null values.

2. `is_white ( )` is used in the `WHERE` clause.

Example

  Given table `T1` having a text attribute `ATTR1` and three records, for example, `line one`, `<Space>`, `<Tab>`, where the second record contains all spaces and the third record contains a tab, then:

         SELECT FROM T1 WHERE is_white (ATTR1);

  Returns the second and third record.

         SELECT FROM T1 WHERE not (is_white (ATTR1));

  Returns the first record: `line one`.

### 10.3.8 length ( )

`length ( )` returns the length of a given string.

Syntax

      length (string)

  where:

  `string` | is any character string of any data type attribute.

Note

  `length ( )` will not handle null values.

Example

  Given a table `T1` having a ten-character attribute name and three records, for example, `Jim`, `John` and `Fred`, then:

         SELECT T1 WHERE length (name) > 3;

  Returns `John` and `Fred`.

         length ("abc")

  Returns `3`.

### 10.3.9 lpad ( )

`lpad ( )` returns a string left-padded by a specified string with a given length.

Syntax

      lpad (string, pad_string [, [occurrence])

  where:

  `string` | `is any character string, CHARACTER or TEXT data type attribute.`
  `pad_string` | `is any character string.`
  `occurrence` | `is an integer number.`

Notes

1. When `occurrence` is specified, it will pad `pad_string` as many times as `occurrence` indicates

2. When `occurrence` is not specified and if `string` is a character string or `TEXT` data attribute, then it will pad `pad_string` only once.

   If `string` is a `CHARACTER` attribute, then it will pad `pad_string` as many times as possible to fill the maximum length of the `CHARACTER` data type.

Example

  The following example:

         lpad ("program", "MY")

  Returns `MYprogram`.

  The following example:

         lpad ("program", "1", 3)

  Returns `111program`.

  Given table `T1` with attribute `(C, CHAR(10,1))` and values `abc`, `123456789`.

         SELECT lpad (C, "MY") FROM T1;

  will produce:

         MYMYMYabc
         123456789

### 10.3.10 lscan ( )

`lscan ( )` returns the position of the nth occurrence of a given string.

Syntax

      lscan (string, search_string [, occurrence ])

  where:

  `string` | is any character string, `CHARACTER` or `TEXT` data type attribute.
  `search_string` | is any character string.
  `occurrence` | is an integer number.

Notes

1. The position of the first character in the source string is 1.

2. If `occurrence` is not specified, the first occurrence will be reported.

3. If the requested occurrence of the search string is not found in the source string, zero is returned.

4. The function name `strindex ( )` can be used as an alias to `lscan ( )`.

5. `lscan ( )` will not handle null values.

Example

  The following example:

         lscan ("abcdef", "cd")

  Returns a value of `3`.

  The following example:

         lscan ("abcdaef", "a", 2)

  Returns a value of `5`.

### 10.3.11 ltrim ( )

`ltrim ( )` trims a specified character string from the left side of a given character string.

Syntax

      ltrim (string [, trim_set])

  where:

  `string` | is any character string, `CHARACTER` of `TEXT` data type attribute.
  `trim_set` | is any character string. The default value is white-space characters including space, tab, new line characters, vertical tab and form feed.

Notes

1. `ltrim ( )` will not handle null values.

2. The order of characters listed in `trim_set` has no bearing on the operation of the function. A trim set of `xy` will yield the same results as a trim set of `yx`.

Example

  The following example:

         ltrim ("abcdef", "ab")

  Returns `cdef`.

  The following example:

         ltrim ("   abc")

  Returns `abc`.

### 10.3.12 nullval ( )

`nullval ( )` returns the default value of an attribute with a null value. If the attribute value is not null, then `nullvall ( )` returns its value.

Syntax

      nullval (attr, default_value)

  where:

  `default_value` | is any value and can also be any attribute that has the same generic data type as `attr`.

Example

  Given a table `T1` having a ten-character attribute name and three records, for example, `null`, `John`, `Fred`:

         SELECT nullval (name, "X") FROM T1;

  Returns `X`, `John`, and `Fred`.

### 10.3.13 round ( )

`round ( )` returns a number rounded to the number of specified decimal digits.

Syntax

      round (number [, digits])

  where:

  `number` | is any numeric value and can be a numeric attribute.
  `digits` | is number of digits after the decimal place. It may take on both positive and negative values and the default is zero digits to the right of the decimal point.

Notes

1. `DOLLAR` data is converted to `FLOAT`.

2. `round ( )` will not handle null values.

Example

  The following example:

         round (12.345)

  Yields a value of `12.0`.

  The following example:

         round (12.345, 2)

  Yields a value of `12.35`.

  The following example:

         round (12.345, -1)

  Yields a value of `10.0`.

### 10.3.14 rpad ( )

`rpad ( )` returns a string right-padded by a specified string with a given length.

Syntax

      rpad (string, pad_string [, [occurrence])

  where:

  `string` | is any character string, `CHARACTER` or `TEXT` data type attribute.
  `pad_string` | is any character string.
  `occurrence` | is an integer number.

Notes

1. When `occurrence` is specified, it will pad `pad_string` as many times as `occurrence` indicates

2. When `occurrence` is not specified and if string is a character string or `TEXT` data attribute, then it will pad `pad_string` only once.

   If `string` is a `CHARACTER` attribute, then it will pad `pad_string` as many times as possible to fill the maximum length of the `CHARACTER` data type.

Example

  The following example:

         rpad ("program", "MY")

  Returns `programMY`.

  The following example:

         rpad ("program", "1", 3)

  Returns `program111`.

  Given table `T1` with attribute `(C, CHAR(10,1))` and values `abc`, `123456789`.

         SELECT rpad (C, "MY") FROM T1;

  will produce:

         abcMYMYMY
         123456789

### 10.3.15 rscan ( )

`rscan ( )` returns the starting position of the nth occurrence of the specified string.

Syntax

      rscan (string, search_string [, occurrence])

  where:

  `string` | is any character string, CHARACTER or TEXT data type attribute.
  `search_string` | is any character string.
  `occurrence` | is an integer number

Notes

1. `rscan ( )` searches from right to left. The position of the leftmost character in the source string is 1.

2. If `occurrence` is not specified, the first occurrence will be reported.

3. If the `search_string` is not found in the source string, zero is returned.

4. The function name `rstrindex ( )` can be used as an alias to `rscan ( )`.

5. `rscan ( )` will not handle null values.

Example

  The following command returns `10`:

         rscan ("John and John", "John")

  The following command returns `1`:

         rscan ("John and John", "John", 2)

  The following command returns `0`:

         rscan (("John and John", "Jim")

### 10.3.16 rstrindex ( )

`rstrindex ( )` returns the starting position of the nth occurrence of the specified string.

Syntax

      rstrindex (string, search_string [, occurrence])

  where:

  `string` | is any character string, CHARACTER or TEXT data type attribute.
  `search_string` | is any character string.
  `occurrence` | is an integer number.

Notes

1. `rstrindex ( )` searches from right to left. The position of the leftmost character in the source string is 1.

2. If `occurrence` is not specified, the first occurrence will be reported.

3. If the `search_string` is not found in the source string, zero is returned.

4. The function name `rscan ( )` can be used as an alias to `rstrindex ( )`.

5. `rstrindex ( )` will not handle null values.

Example

  The following command returns `10`:

         rstrindex ("John and John", "John")

  The following command returns `1`:

         rstrindex ("John and John", "John", 2)

  The following command returns `0`:

         rstrindex (("John and John", "Jim")

### 10.3.17 rtrim ( )

`rtrim ( )` trims a specified character string from the right side of a given character string.

Syntax

      rtrim (string [, trim_set])

  where:

  `string` | is any character string or INTEGER, CHARACTER or TEXT data type attribute.
  `trim_set` | is any character string. It defaults to white-space characters including space, tab, new line characters, vertical tab and form feed.

Notes

1. All characters to the left of the first non- `trim_set` character in string are transferred to the resulting string.

2. `rtrim ( )` will not handle null values.

3. The order of characters listed in `trim_set` has no bearing on the operation of the function. A trim set of `xy` will yield the same results as a trim set of `yx`.

Example

  The following command returns `abcdef`:

         rtrim ("abcdef")

  The following command returns `abcd`:

         rtrim ("abcdef", "fe")

  The following command returns `abcd`:

         rtrim ("abcdef", "ef")

### 10.3.18 sign ( )

`sign ( )` returns a value to indicate whether a given numeric value is positive, negative or zero.

Syntax

      sign (number)

  where:

  `number` | can be any numeric value and can also be any attribute of numeric data type or a result of an expression.

Notes

1. 1 is returned when `number` is positive, 0 is returned when `number` is zero and -1 is returned when `number` is negative.

2. `sign ( )` will not handle null values.

Example

  The following command returns `-1`:

         sign (150-700)

  The following command returns `0`:

         sign (150-150)

  The following command returns `1`:

         sign (700-500)

### 10.3.19 strdel ( )

`strdel ( )` returns a given string with characters deleted according to the specified position and optional length argument.

Syntax

      strdel (string, position [, length])

  where:

  `string` | is any character string, `CHARACTER` or `TEXT` data type attribute.
  `position` | is the position within the string.
  `length` | is an integer number which indicates the number of characters that will be deleted from the character specified by `position`. It can also be an `INTEGER` attribute. The default is 1.

Notes

1. `strdel ( )` works on attributes of type `CHAR`, `TEXT` and `BULK`.

2. `strdel ( )` will handle null values.

Example

  The following command returns `ac`:

         strdel ("abc", 2)

### 10.3.20 strindex ( )

`strindex ( )` returns the position of the nth occurrence of a given string.

Syntax

      strindex (string, search_string [, occurrence])

  where:

  `string` | is any character string, `CHARACTER` of `TEXT` data type attribute.
  `search_string` | is any character string.
  `occurrence` | is an integer number.

Notes

1. The position of the first character in the source string is 1.

2. If `occurrence` is not specified, the first occurrence will be reported.

3. If the requested occurrence of the `search_string` is not found in the source string, zero is returned.

4. The function name `lscan ( )` can be used as an alias to `strindex ( )`.

5. `strindex ( )` will not handle null values.

Example

  The following command returns `3`:

         strindex ("abcdef", "cd")

  The following command returns `5`:

         strindex ("abcdaef", "a", 2)

### 10.3.21 strins ( )

`strins ( )` returns a string with another string inserted in front of a specific character position.

Syntax

      strins (string1, position, string2)

  where:

  `string1` | is any character string.
  `string2` | is any character string.
  `position` | is a numeric value.

Notes

1. Position 1 is taken to be the first character of the string.

2. `strins ( )` does not work with values of type `BULK`. To achieve similar results with values of type `BULK`, a combination of `substr ( )` and `concat` can be used.

3. `strins ( )` will not handle null values.

Example

  The following command returns `abcd`:

         strins ("ad", 2, "bc")

### 10.3.22 substr ( )

`substr ( )` returns a substring of of a given string, starting at specified character position and ending at the another character position.

Syntax

      substr (string, position [, length ])

  where:

  `string` | is any character string or, `CHARACTER` or `TEXT` data type attribute.
  `position` | is a numeric value.
  `length` | is a numeric value. It defaults to the remaining length of the source string.

Notes

1. `substr ( )` will work with values of type `BULK`, `CHAR` or `TEXT`.

2. `substr ( )` will handle null values.

Example

  The following command returns `bcde`:

         substr ("abcde", 2)

  The following command returns `bc`:

         substr ("abcde", 2, 2)

  The following command returns `4XX56`:

         strins (subtr ("12345678910", 4, 3), 2, "XX")

### 10.3.23 tolower ( )

`tolower ( )` returns a given string with all alphabetic characters changed to lowercase.

Syntax

      tolower (string)

  where:

  `string` | is any character string, `CHARACTER` or `TEXT` data type attribute.

Example

  The following command returns `foobar7`:

         tolower ("FooBar7")

### 10.3.24 toupper ( )

`toupper ( )` returns given string with all alphabetic characters changed to uppercase.

Syntax

      toupper (string)

  where:

  `string` | is any character string, `CHARACTER` or `TEXT` data type attribute.

Notes

  `toupper ( )` will not handle null values.

Example

  The following command returns `FOOBAR 7`:

         toupper ("Foobar 7")

### 10.3.25 trunc ( )

`trunc ( )` returns a number truncated to a specified number of digits following the decimal point.

Syntax

      trunc (number [, digits])

  where:

  `number` | is any numeric value or numeric attribute or an expression.
  `digits` | is the number of digits after the decimal place. It can take on both positive and negative values and the default value is zero.

Notes

  `trunc ( )` will not handle null values.

Example

  The following command returns a value of `12.00`.

         trunc (12.345)

  The following command returns a value of `12.34`.

         trunc (12.345, 2)

  The following command returns a value of `12500.00`.

         trunc (12506.78, -2)

  The following command returns a value of `30.00`.

         trunc (10+20.25)

## 10.4 Math Library Functions

The math functions available vary from system to system. They will be a subset of the following. For which are available on your system and for full information on individual functions, see your system documentation.

The following is a list of Math Library Functions and their brief descriptions:

Table 10-2: Math Library Functions

Math Library Functions | Description
`acos ( )` | Returns the arccosine of the entered value in the range 0 to pi.
`asin ( )` | Returns the arcsine of the entered value in the range -pi/2 to pi/2.
`atan ( )` | Returns the arctangent of the entered value in the range -pi/2 to pi/2.
`atan2 ( )` | Returns the arctangent of two values in the range -pi to pi. The signs of both arguments determine the quadrant of the returned value.
`ceil ( )` | Returns the smallest integer not less than the entered value.
`cos ( )` | Returns the cosine of the entered value; the value entered is taken to be in radians.
`cosh ( )` | Returns the hyperbolic cosine of the entered value.
`erf ( )` | Returns the error function of the entered value.
`erfc ( )` | Returns a value used to correct the loss of accuracy of `erf ( )` for large entered values.
`exp ( )` | Returns ex, where x is the value of the argument.
`fabs ( )` | Returns the absolute value of the argument.
`floor ( )` | Returns the largest integer smaller than the entered value.
`fmod ( )` | When two values x and y are entered, `fmod ( )` returns the floating point remainder of the division of x by y. Zero is returned in the case of division by zero or if the resulting value would overflow.
`frexp ( )` | When two values x and y are entered, `frexp ( )` returns the mantissa of x, and the exponent in the location pointed to by y.
`frexpman ( )` | Returns the mantissa of the argument.
`frexpexp ( )` | Returns the exponent of the argument.
`gamma ( )` | Returns the value of the gamma function of the entered value. The value entered must be positive.
`hypot ( )` | Given two values x and y, `hypot ( )` returns the square root of x2 + y2.
`j0 ( )` | Returns the Bessel function of the entered value of the fist kind of order 0.
`j1 ( )` | Returns the Bessel function of the entered value of the fist kind of order 1.
`jn ( )` | Returns the Bessel function of the entered value of the fist kind of order n.
`ldexp ( )` | Returns the quality value `ldexp (x, i) = x*2i`.
`log ( )` | Returns the natural logarithm of the entered value. The value entered must be positive.
`log10 ( )` | Returns the logarithm base 10 of the entered value. The value entered must be positive.
`modf ( )` | Given two values x and y, `modf ( )` returns the fractional part of x, and stores the integral part in the location pointed at by y.
`modffrac ( )` | Returns the fractional part of the entered value.
`modfint ( )` | Returns the integral part of the entered value.
`pow ( )` | Given two values x and y, `pow ( )` returns xy. If x is zero, y must be positive. If x is negative, y must be an integer.
`sin ( )` | Returns the sine of the entered value. The value entered is taken to be in radians.
`sinh ( )` | Returns the hyperbolic sine of the entered value.
`sqrt ( )` | Returns the square root of the entered value. The value entered must be positive.
`tan ( )` | Returns the tangent of the entered value. The value entered is taken to be in radians.
`tanh ( )` | Returns the hyperbolic tangent of the entered value.
`y0 ( )` | Returns the Bessel function of the entered value of the second kind of order 0. The value entered must be positive.
`y1 ( )` | Returns the Bessel function of the entered value of the second kind of order 1. The value entered must be positive.
`yn ( )` | Returns the Bessel function of the entered value of the second kind of order n. The value entered must be positive.