Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Scalar Functions


The following sections describe Mimer SQL's scalar functions.

ABS

Returns the absolute value of the given numeric expression.

Syntax

Syntax for the ABS function:



value is a numeric or an interval value expression.

Rules

Example

 SET INT_VAL = ABS(-15);  -- sets INT_VAL to 15

ASCII_CHAR

Returns the character that has the given ASCII code value. The given ASCII code value should be in the range 0-255.

Syntax

Syntax for the ASCII_CHAR function:



code is a numeric expression representing an ASCII value.

Rules

Example

 SET CHR_VAL = ASCII_CHAR(65);  -- sets CHR_VAL to 'A'

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

Mimer SQL Experience
The BIT_LENGTH function is not supported. The function OCTET_LENGTH's return value multiplied by 8 can be used as an alternative.
 SET INT_VAL = OCTET_LENGTH(X'4142') * 8;  -- sets INT_VAL to 16

Example

 SET INT_VAL = BIT_LENGTH(X'4142');  -- sets INT_VAL to 16

BUILTIN.UTC_TIMESTAMP

Returns a TIMESTAMP denoting the current Coordinated Universal Time.

Syntax

Syntax for the BUILTIN.UTC_TIMESTAMP function:



Rules

Examples

 SQL>SELECT BUILTIN.UTC_TIMESTAMP() AS utcts FROM system.onerow;
 
 utcts
 =====
 2012-10-30 14:55:22.643082
 
 One row found
 
 
 CREATE TABLE EVENTS(ID INTEGER PRIMARY KEY,
                     UTCTS TIMESTAMP);
 INSERT INTO EVENTS(ID) VALUES (1, BUILTIN.UTC_TIMESTAMP());
 UPDATE EVENTS
   SET    ID = ID + 5, UTCTS = BUILTIN.UTC_TIMESTAMP()
   WHERE  ID = 10;

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

CEILING

Returns the smallest integer greater than or equal to a numeric expression.

Syntax

Syntax for the CEILING function:



value is a numeric value expression.

Rules

Example

 SET ? = CEILING(3.57);     -- returns 4
 SET ? = CEILING(-3.57);    -- returns -3
 SET ? = CEILING(1.2345e3); -- returns 1.235000000E+003

CURRENT_DATE

Returns a DATE value denoting the current date (i.e. today).

Syntax

Syntax for the CURRENT_DATE function:



Rules

Example

 UPDATE sometable SET usercnt = 13, updated = CURRENT_DATE;

CURRENT_PROGRAM

Returns the name of an entered program.

Syntax

Syntax for the CURRENT_PROGRAM function:



Rules

Example

The following example returns the PROGRAM ident if entered, otherwise the session ident:

 SET CHR_STR = COALESCE(CURRENT_PROGRAM(), SESSION_USER);

CURRENT_USER

Returns the name of the currently connected USER ident or the PROGRAM ident that is currently entered.
When used in a routine or trigger, it returns the name of the creator of the schema to which the routine or trigger belongs.

Syntax

Syntax for the CURRENT_USER function:



Rules

Example

 CREATE DOMAIN NAME AS NCHAR VARYING(128) DEFAULT CURRENT_USER;

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 FOR CHARGE_PERIOD_NO_SEQUENCE;

DACOS

Returns the arccosine for a numeric expression.

Syntax

Syntax for the DACOS function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DACOS function is not supported.

DASIN

Returns the arcsine for a numeric expression.

Syntax

Syntax for the DASIN function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DASIN function is not supported.

DATAN

Returns the arctangent for a numeric expression.

Syntax

Syntax for the DATAN function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DATAN function is not supported.

DATAN2

Returns the arctangent for the tangent between 2 numeric expressions.

Syntax

Syntax for the DATAN2 function:



value-1 and value-2 are numeric value expressions. The function handles values that are within the range of a double precision expression.

The DATAN2 function calculates the arctangent of the two parameters value-1 and value-2. It is similar to calculating the arctangent of value-2 / value-1, except that the signs of both arguments are used to determine the quadrant of the result. Effectively, this means that DATAN2(value-1, value-2) finds the counterclockwise angle in radians between the x-axis and the vector <value-2, value-1> in 2-dimensional Euclidean space.

Rules

Mimer SQL Experience
The DATAN2 function is not supported.

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
 
 SET INT_VAL = DAYOFYEAR(DATE'2011-11-10');  -- sets INT_VAL to 314
 SET INT_VAL = DAYOFYEAR(DATE'2012-11-10');  -- sets INT_VAL to 315

DCOS

Returns the cosine for a numeric expression.

Syntax

Syntax for the DCOS function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DCOS function is not supported.

DCOT

Returns the cotangent for a numeric expression.

Syntax

Syntax for the DCOT function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DCOT function is not supported.

DDEGREES

Returns an angle expressed in radians as degrees.

Syntax

Syntax for the DDEGREES function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DDEGREES function is not supported.

DEXP

Returns the exponential value for a numeric expression.

Syntax

Syntax for the DEXP function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DEXP function is not supported.

DLOG

Returns the natural logarithm for a numeric expression.

Syntax

Syntax for the DLOG function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DLOG function is not supported.

DLOG10

Returns the base-10 logarithm for a numeric expression.

Syntax

Syntax for the DLOG10 function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DLOG10 function is not supported.

DPOWER

Returns the specified numeric expression, raised to the power of the given value.

Syntax

Syntax for the DPOWER function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DPOWER function is not supported.

DRADIANS

Returns an angle expressed in degrees as radians.

Syntax

Syntax for the DRADIANS function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DRADIANS function is not supported.

DSIN

Returns the sine for a numeric expression.

Syntax

Syntax for the DSIN function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DSIN function is not supported.

DSQRT

Returns the square root of a numeric expression.

Syntax

Syntax for the DSQRT function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DSQRT function is not supported.

DTAN

Returns the tangent for a numeric expression.

Syntax

Syntax for the DTAN function:



value is a numeric value expression. The function handles values that are within the range of a double precision expression.

Rules

Mimer SQL Experience
The DTAN function is not supported.

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'

FLOOR

Returns the largest integer less than or equal to a numeric expression.

Syntax

Syntax for the FLOOR function:



value is a numeric value expression.

Rules

Example

 SET ? = FLOOR(13.13);    -- returns 13
 SET ? = FLOOR(-13.13);   -- returns -14
 SET ? = FLOOR(-12.34E1); -- returns -1.240000000E+002

INDEX_CHAR

Returns the index character for a string.

Syntax

Syntax for the INDEX_CHAR function:



value is a character value expression

Rules

Examples

 SELECT INDEX_CHAR('östra aros' COLLATE english_1) FROM... -- will return 'O'
 SELECT INDEX_CHAR('östra aros' COLLATE swedish_1) FROM... -- will return 'Ö'

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

LOCALTIME

Returns a TIME value denoting the current time (i.e. now).

Syntax

Syntax for the LOCALTIME function:



seconds-precision is an unsigned integer value denoting the seconds precision for the returned TIME value.

Rules

Example

 UPDATE EVENTS SET ADJUSTED = LOCALTIME   -- sets ADJUSTED to current time
 WHERE ID = 81;                           -- (e.g. 15:45:02)

LOCALTIMESTAMP

Returns a TIMESTAMP denoting the current date and time.

Syntax

Syntax for the LOCALTIMESTAMP function:



seconds-precision is an unsigned integer value denoting the seconds precision for the returned TIMESTAMP value.

Rules

Example

 CREATE TABLE EVENTS(ID  INTEGER    PRIMARY KEY,
                     TS  TIMESTAMP  DEFAULT LOCALTIMESTAMP);
 INSERT INTO EVENTS(ID) VALUES (1);  -- default value for TS inserted
                                     -- (e.g. 2012-09-27 16:14:07.230000)
 UPDATE EVENTS
 SET    TS = LOCALTIMESTAMP
 WHERE  ID <= 10;

LOWER

Converts all uppercase letters in a character string to lowercase.

Syntax

Syntax for the LOWER function:



source-string is a character string expression.

Rules

Note: The length of the result may be longer or shorter than the input value. This means that using LOWER (or UPPER) on a column may cause data truncation.

Example

 SELECT CHAR_LENGTH(TRIM(DESCRIPTION)), LOWER(TRIM(DESCRIPTION))
 FROM   CHARGES;

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 the last value in that series has not already been reached.

Syntax

Syntax for the NEXT VALUE function:



Rules

Example

 SET Z = NEXT VALUE FOR Z_SEQUENCE;
 
Note: If the NEXT VALUE function is used in a select clause the sequence will be incremented for each row returned by the query.

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
 SET INT_VAL = OCTET_LENGTH('ABC');    -- sets INT_VAL to 3
 SET INT_VAL = OCTET_LENGTH(n'ABC');   -- sets INT_VAL to 12

PASTE

Returns a character string where a specified number of characters, beginning at a given position, have been deleted from a character string and replaced with a given string expression.

Syntax

Syntax for the PASTE function:



string-1 and string-2 are character or binary string expressions.

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

start-position and string-length are integer value expressions.

Rules

Example

 SET CHR_STR = PASTE('TEST STRING', 6, 3, 'P');  -- sets CHR_STR to 'TEST PING'

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

REPEAT

Returns a character string composed of a specified string expression repeated a given number of times.

Syntax

Syntax for the REPEAT function:



sub-string is a character or binary string expression.

repeat-count is an integer expression.

Rules

Example

 SET CHR_STR = REPEAT('ABC', 3);  -- sets CHR_STR to 'ABCABCABC'

REPLACE

Replaces all occurrences of a given string expression with another string expression in a character string.

Syntax

Syntax for the REPLACE function:



source-string, string-1 and string-2 are character or binary string expressions.

source-string, string-1 and string-2 must be of equal type, i.e. either all are character or all are binary.

Rules

Example

 SET CHR_STR = REPLACE('TEST STRING', 'ST', 'NOR');  -- sets CHR_STR to
                                                     -- 'TENOR NORRING'

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

SESSION_USER

Returns the name of the currently connected ident.

Syntax

Syntax for the SESSION_USER function:



Rules

Example

The following example returns the Program ident if entered, otherwise the session ident:

 SET CHR_STR = COALESCE(CURRENT_PROGRAM(), SESSION_USER);

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

SOUNDEX

Returns a character string value containing six digits that represent an encoding of the sound of the given string expression.

Syntax

Syntax for the SOUNDEX function:



source-string is a character string expression.

Rules

SUBSTRING

Extracts a substring from a given string, according to specified start position and length of the substring.

Syntax

Syntax for the SUBSTRING function:



source-string is a character or binary string expression.

start-position and string-length are integer value expressions.

Rules

 CHAR_LENGTH(source-string) + 1 - start-position
 

i.e. the remainder of source-string, starting at start-position, is returned.

Example

 SET CHR_STR = SUBSTRING('Whatever' FROM 3 FOR 3); -- sets CHR_STR to 'ate'

TAIL

Returns the specified number of rightmost characters in a given character string.

Syntax

Syntax for the TAIL function:



source-string is a character or binary string expression.

count is an integer value expression.

Rules

Example

 SET CHR_STR = TAIL('TEST STRING', 3);  -- sets CHR_STR to 'ING'

TRIM

Removes leading and/or trailing instances of a specified character from a string.

Syntax

Syntax for the TRIM function:



trim-character is a character or binary string expression of length 1.

source-string is a character or binary string expression.

source-string and trim-character must be of equal type, i.e. either must both be character or both binary.

Note: LEADING, TRAILING or BOTH is referred to as the trim-specification below.

Rules

Examples

 SET CHR_STR = TRIM(' TEST ');                  -- sets CHR_STR to 'TEST'
 SET CHR_STR = TRIM('T' FROM 'TEST');           -- sets CHR_STR to 'ES'
 SET CHR_STR = TRIM(LEADING 'T' FROM 'TEST');   -- sets CHR_STR to 'EST'
 SET CHR_STR = TRIM(TRAILING 'T' FROM 'TEST');  -- sets CHR_STR to 'TES'

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

UNICODE_CHAR

Returns the character that has the given Unicode scalar value.

Syntax

Syntax for the UNICODE_CHAR function:



code is a numeric expression representing a Unicode scalar value.

Rules

Example

 SET NCHR_VAL = UNICODE_CHAR(65);  -- sets NCHR_VAL to 'A'

UNICODE_CODE

Returns the Unicode scalar value of the leftmost character in the given string expression, as an integer.

Syntax

Syntax for the UNICODE_CODE function:



source-string is a character or binary string expression.

Rules

Example

 SET INT_VAL = UNICODE_CODE(n'A');  -- sets INT_VAL to 65

UPPER

Converts all lowercase letters in a character string to uppercase.

Syntax

Syntax for the UPPER function:



source-string is a character string expression.

Rules

Note: The length of a result may be longer or shorter than the input value. This means that using UPPER on a column may cause data truncation.

USER

Returns the same value as CURRENT_USER. We recommend that you use CURRENT_USER, see CURRENT_USER.

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

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX