Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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
Mimer SQL Documentation TOC PREV NEXT INDEX