Scalar Numeric Functions

http://developer.mimer.com

Scalar Numeric Functions

The following sections describe scalar numeric functions.

ASCII_CODE

Returns the ASCII code value of the leftmost character in the given string expression, as an integer.
Syntax

Syntax for the ASCII_CODE function:

source-string is a character or binary string expression.

Rules

A single INTEGER value is returned, representing an ASCII code.
If the source-string contains more than one character, the ASCII code of the left-most octet is returned.
If the length of source-string is zero, then the result of the function is NULL.
If the value of source-string is NULL, then the result of the function is NULL.

Example
 SET INT_VAL = ASCII_CODE('A');  -- sets INT_VAL to 65
BIT_LENGTH

Returns the number of bits in a string.
Syntax

Syntax for the BIT__LENGTH function:

source-string is a character or binary string expression.

Rules

BIT_LENGTH returns an INTEGER value.
If the data type of source-string is variable-length character or variable-length binary, then the result of BIT_LENGTH is the same as the actual length of source-string multiplied with 8. (The number of bits in an octet)
If the data type of source-string is fixed-length character or fixed-length binary, then the result of BIT_LENGTH is the same as the fixed-length of source-string multiplied by 8.
If the value of source-string is NULL, then the result of the function is NULL.

Example
 SET INT_VAL = BIT_LENGTH(X'4142');  -- sets INT_VAL to 16
CHAR_LENGTH or CHARACTER_LENGTH

Returns the length of a string.
Syntax

Syntax for the CHAR_LENGTH (or CHARACTER_LENGTH) function:

source-string is a character or binary string expression.

Rules

CHAR_LENGTH returns an INTEGER value.
If the data type of source-string is variable-length character or variable-length binary, then the result of CHAR_LENGTH is the same as the actual length of source-string.
If the data type of source-string is fixed-length character or fixed-length binary, then the result of CHAR_LENGTH is the same as the fixed-length of source-string.
If the value of source-string is NULL, then the result of the function is NULL.

Example
 SET INT_VAL = CHAR_LENGTH('TEST STRING');  -- sets INT_VAL to 11
CURRENT_VALUE

Returns the current value of a sequence.
Syntax

Syntax for the CURRENT_VALUE function:

Rules

The result is the current value of the sequence specified in sequence_name. This is the value that was returned when the NEXT_VALUE function was used for this sequence in this session.
This function cannot be used until the initial value has been established for the sequence by using NEXT_VALUE (i.e. using it immediately after the sequence has been created will raise an error).
The function can be used where a value-expression would normally be used. It can also be used after the DEFAULT clause in the CREATE DOMAIN, CREATE TABLE and ALTER TABLE statements.
USAGE privilege must be held on the sequence in order to use it.

Example
 CREATE DOMAIN CHARGE_PERIOD_VALUE AS INTEGER
 DEFAULT CURRENT_VALUE OF CHARGE_PERIOD_NO_SEQUENCE;
DAYOFWEEK

Returns the day of the week for the given date expression, expressed as an integer value in the range 1-7, where 1 represents Monday.
Syntax

Syntax for the DAYOFWEEK function:

date-or-timestamp is a date or timestamp value expression.

Rules

The result is an integer value, 1 through 7, where 1 = Monday, 2 = Tuesday and so on.
If the value of date-or-timestamp is NULL, then the result of the function is NULL.

DAYOFYEAR

Returns the day of the year for the given date expression, expressed as an integer in the range 1-366.
Syntax

Syntax for the DAYOFYEAR function:

date-or-timestamp is a date or timestamp value expression.

Rules

The result is an integer value, 1 through 366, where 1 = January 1st.
The value for a day after February 28th depends on whether the year is a leap year or not.
If the value of date-or-timestamp is NULL, then the result of the function is NULL.

Example
 SET INT_VAL = DAYOFYEAR(CURRENT_DATE);  -- sets INT_VAL to the 
                                         -- day number of the current year
EXTRACT

Extracts a single field from a DATETIME or INTERVAL value.
Syntax

Syntax for the EXTRACT function:

Rules

field-name is one of: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.
value must be of type DATETIME or INTERVAL and it must contain the field specified by field-name, otherwise an error is raised.
The data type of the result is exact numeric with a precision equal to the leading precision of value and a scale of 0.
The exception is when field-name is SECOND, in which case the precision is equal to the sum of the leading precision and the seconds precision of value, with a scale equal to the seconds precision.

When value is a negative INTERVAL, the result is a negative value. In all other cases the result is a positive value.
If the value of value is NULL, then the result of the function is NULL.

Example
 SELECT CASE EXTRACT (MONTH FROM ARRIVE)
    WHEN 1 THEN 'JANUARY'
 ...
IRAND

Returns a random integer number.
Syntax

Syntax for the IRAND function:

seed is an integer value expression

Rules

The result is a random integer value.
If a seed is given, this value is used to calculate the random value. If no seed is given, the value is calculated from the previous value. It is thus possible to generate the same random sequence by using the same seed.

Example
 SET INT_VAL = MOD(IRAND(), 5);  -- sets INT_VAL to a random 
                                 -- value between 0 and 4
MOD

Returns the remainder (modulus) of a specified integer expression divided by a second specified integer expression.
Syntax

Syntax for the MOD function:

integer-expression-1 and integer-expression-2 are integer value expressions.

Rules

The result is the remainder of integer-expression-1 divided by integer-expression-2.
If the value of integer-expression-2 is zero, a divide-by-zero error will be raised.
The sign of the result is the same as the sign of integer-expression-1.
If the value of either operand is NULL, then the result of the function is NULL.

Example
 SET INT_VAL = MOD(IRAND(), 5);  -- sets INT_VAL to a random 
                                 -- value between 0 and 4
NEXT_VALUE

Returns the next value in the series of values defined by a sequence, provided that last value in that series has not already been reached.
Syntax

Syntax for the NEXT_VALUE function:

Rules

The result will be the next value in the series of the values defined by the sequence specified in sequence_name (this value will then become the current value for the sequence).
If the sequence is unique and the current value of the sequence specified in sequence_name is already equal to the last value in the series of the values defined by it an error will be raised and the current value of the sequence will remain unchanged.
If the sequence is non-unique, the function will always succeed. If the current value of the sequence specified in sequence_name is equal to the last value in the series of values generated by the sequence, the initial value of the sequence will be returned.
The function can be used where a value-expression would normally be used. It can also be used after the DEFAULT clause in the CREATE DOMAIN, CREATE TABLE and ALTER TABLE statements.
This function is used to establish the initial value of the sequence after it has been created using the CREATE SEQUENCE statement.
USAGE privilege must be held on the sequence in order to use it.

Example
 SET Z = NEXT_VALUE OF Z_SEQUENCE;
OCTET_LENGTH

Returns the octet (byte) length of a string. For single-octet character sets this is the same as CHARACTER_LENGTH.
Syntax

Syntax for the OCTET_LENGTH function:

source-string is a character or binary string expression.

Rules

OCTET_LENGTH returns an INTEGER value.
If the data type of source-string is variable-length character or variable length binary, then the result of OCTET_LENGTH is the same as the actual length of source-string in octets.
If the data type of source-string is fixed-length character or fixed-length binary, then the result of OCTET_LENGTH is the same as the fixed-length of source-string.
If the value of source-string is NULL, then the result of the function is NULL.

Example
 SET INT_VAL = OCTET_LENGTH(X'4142');  -- sets INT_VAL to 2
POSITION

Returns the starting position of the first occurrence of a specified string expression in a given character string, starting from the left of the character string.
Syntax

Syntax for the POSITION function:

sub-string and source-string are character or binary string expressions.

sub-string and source-string must be of the same type, i.e. either both character or both binary.

Rules

The position of the first occurrence of sub-string in source-string is returned, starting from position 1 in source-string (the left-most position).
If sub-string does not occur in source-string, the functions returns zero.
If the length of source-string is zero, the function returns zero.
If the length of sub-string is zero, the function returns 1.
If the value of either operand is NULL, then the result of the function is NULL.

Example
 SET INT_VAL = POSITION('STR' IN 'TEST STRING');  -- sets INT_VAL to 6
ROUND

Returns the given numeric expression rounded to the number of places to the right of the decimal point specified by a given integer expression.

If the integer expression is negative, the numeric expression is rounded to a number of places to the left of the decimal point specified by the absolute value of the integer expression.
Syntax

Syntax for the ROUND function:

numeric-value is an integer or a float value expression.

integer-value is an integer value expression.

Rules

If integer-value is positive, the value describes the number of digits permitted in numeric-value, after rounding, to the right of the decimal point, if it is negative it describes the number of digits allowed to the left of the decimal point.
The value returned depends on the data type of numeric-value.
If the value of either operand is NULL, then the result of the function is NULL.

Examples
 SET NUM_VAL = ROUND(762.847, 2);   -- sets NUM_VAL to 762.850
 SET NUM_VAL = ROUND(762.847, 1);   -- sets NUM_VAL to 762.800
 SET NUM_VAL = ROUND(762.847, 0);   -- sets NUM_VAL to 763.000
 SET NUM_VAL = ROUND(762.847, -1);  -- sets NUM_VAL to 760.000
 SET NUM_VAL = ROUND(762.847, -2);  -- sets NUM_VAL to 800.000
SIGN

Returns an indicator of the sign of the given numeric expression.

If the numeric expression is less than zero, -1 is returned. If the numeric expression is equal to zero, 0 is returned. If the numeric expression is greater than zero, 1 is returned.
Syntax

Syntax for the SIGN function:

numeric-value is an integer or a float value expression.

Rules

The function returns an indicator of the sign of numeric-value. If numeric-value is less than zero, -1 is returned. If numeric-value equals zero, 0 is returned. If numeric-value is greater than zero, 1 is returned.
If the value of numeric-value is NULL, then the result of the function is NULL.

Examples
 SET INT_VAL = SIGN(-12);  -- sets INT_VAL to -1
 SET INT_VAL = SIGN(0);    -- sets INT_VAL to 0
 SET INT_VAL = SIGN(12);   -- sets INT_VAL to 1
TRUNCATE

Returns the given numeric expression truncated to a number of places to the right of the decimal point specified by a given integer expression.

If the integer expression is negative, the numeric expression is truncated to a number of places to the left of the decimal point specified by the absolute value of the integer expression.
Syntax

Syntax for the TRUNCATE function:

numeric-value is an integer or a float value expression.

integer-value is an integer value expression.

Rules

If integer-value is positive, the value describes the number of digits permitted in numeric-value, after truncation, to the right of the decimal point.
If it is negative, it describes the number of digits allowed to the left of the decimal point.

The value returned depends on the data type of numeric-value.
If the value of either operand is NULL, then the result of the function is NULL.

Examples
 SET NUM_VAL = TRUNCATE(25.89, 1);   -- sets NUM_VAL to 25.80
 SET NUM_VAL = TRUNCATE(25.89, -1);  -- sets NUM_VAL to 20.00
WEEK

Returns the week of the year for the given date expression, expressed as an integer value in the range 1-53.
Syntax

Syntax for the WEEK function:

date-or-timestamp is a date or timestamp value expression.

Rules

The result is an integer value, 1 through 53, representing the week number in the year, calculated in accordance with the ISO 8601 standard.
If the value of date-or-timestamp is NULL, then the result of the function is NULL.

Example
 SET INT_VAL = WEEK(CURRENT_DATE);  -- sets INT_VAL to the week number
                                    -- of the current year

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se