## Scalar Functions

The following sections describe Mimer SQL's scalar functions.

## ABS

Returns the absolute value of the given numeric expression.

## Syntax

`value`

is a numeric or an interval value expression.## Rules

- The function returns the absolute value of
`value`

.- If the value of
`value`

is`NULL`

, then the result of the function is`NULL`

.## 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

- If the value of
`code`

is between 0 and 255, the function returns a single character value, i.e.`CHAR(1)`

, otherwise the function returns`NULL`

. (For`code`

values above 255, use the`UNICODE_CHAR`

function instead. See UNICODE_CHAR.)- If the value of
`code`

is`NULL`

, then the result of the function is`NULL`

.## 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

- 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 by 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 data type of
`source-string`

is fixed-length national character (i.e.`NCHAR`

), then the result of`BIT_LENGTH`

is the same as the fixed-length of`source-string`

multiplied by 32 (the number of bits used to store a national character.)- If the data type of
`source-string`

is variable-length national character, then the result of`BIT_LENGTH`

is the same as the actual length of`source-string`

multiplied by 32.- If the value of
`source-string`

is`NULL`

, then the result of the function is`NULL`

.

## 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.See OCTET_LENGTH.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

- The result is the current Coordinated Universal Time as a
`TIMESTAMP`

value.- All references to
`BUILTIN.UTC_TIMESTAMP`

are effectively evaluated simultaneously from a single reading of the server clock. Thus the conditional expression`BUILTIN.UTC_TIMESTAMP() = BUILTIN.UTC_TIMESTAMP()`

is guaranteed to always evaluate to true.## 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

`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## 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

- The function returns the nearest integer value that is equal or higher to
`value`

.- If the value of
`value`

is`NULL`

, then the result of the function is`NULL`

.- The return data type is based on the input data type. For DECIMAL input, the return data type is integer.
## 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

- The result is the current date (i.e. today) as a
`DATE`

value.- All references to
`CURRENT_DATE`

are effectively evaluated simultaneously from a single reading of the server clock. Thus the conditional expression`CURRENT_DATE = CURRENT_DATE`

is guaranteed to always evaluate to true.- The value of
`CURRENT_DATE`

will always be equal to the`DATE`

portion of`LOCALTIMESTAMP`

.## 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

- The function returns the value of the most recently entered program as nchar varying value with a maximum length of 128, with the collation SQL_IDENTIFIER.
- If no program has been entered the result of the function is
`NULL`

.## 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

- When used in a routine or trigger, the result is the name of the creator of the schema to which the routine or trigger belongs, otherwise the value is the name of the connected ident or the program that was entered.
- The data type of the returned value is nchar varying with a maximum length of 128, with the collation SQL_IDENTIFIER.
## Example

CREATE DOMAIN NAME AS NCHAR VARYING(128) collate SQL_IDENTIFIER DEFAULT CURRENT_USER;## 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 can not 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 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

- The functions returns the arccosine for the value expressed as radians. The data type for the result is double precision. Valid input values are in the range -1 to 1.
- If the value of
`value`

is NULL, then the result of the function is NULL.## 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

- The functions returns the arcsine for the value expressed as radians. The data type for the result is double precision. Valid input values are in the range -1 to 1.
- If the value of
`value`

is NULL, then the result of the function is NULL.## 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

- The functions returns the arctangent for the value expressed as radians. The data type for the result is double precision.
- If the value of
`value`

is NULL, then the result of the function is NULL.## 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

- Returns the angle, in radians, whose tangent is between the two given value expressions. The data type for the result is double precision.
- If the value of
`value-1`

or`value-2`

is NULL, then the result of the function is NULL.## 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 1.- The value for a day after February 28 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 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

`value`

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

- The functions returns the cosine for the values expressed as radians. The data type for the result is double precision.
- If the value of
`value`

is NULL, then the result of the function is NULL.## DCOT

Returns the cotangent for a numeric expression.

## Syntax

`value`

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

- The function returns the cotangent for the value, expressed as radians. The data type for the result is double precision.
- If the value of
`value`

is NULL, then the result of the function is NULL.## 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

- The function converts a numeric expression in radians to the corresponding values expressed in degrees. The data type for the result is double precision.
- If the value of
`value`

is NULL, then the result of the function is NULL.## DEXP

Returns the exponential value for a numeric expression.

## Syntax

`value`

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

- The function returns exponential value for the value expression. The data type for the result is double precision.
- If the value of
`value`

is NULL, then the result of the function is NULL.## DLOG

Returns the natural logarithm for a numeric expression.

## Syntax

`value`

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

- The function returns the natural logarithm for the value expression. The data type for the result is double precision. Valid input values are > 0.
- If the value of
`value`

is NULL, then the result of the function is NULL.## 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

- The function returns the base-10 logarithm for the value expression. The data type for the result is double precision. Valid input values are > 0.
- If the value of
`value`

is NULL, then the result of the function is NULL.## 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

- The function returns the value of the first argument raised to the power of the second argument. The data type for the result is double precision.
- If the value of
`value-1`

or`value-2`

is NULL, then the result of the function is NULL.## 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

- The function converts a value expressed in degrees to the corresponding value expressed as radians. The data type for the result is double precision.
- If the value of
`value`

is NULL, then the result of the function is NULL.## DSIN

Returns the sine for a numeric expression.

## Syntax

`value`

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

- The function returns the sine for the value expressed as radians. The data type for the result is double precision.
- If the value of
`value`

is NULL, then the result of the function is NULL.## 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

- The function returns the square root of the value. The data type for the result is double precision. Valid input values are greater than or equal to 0.
- If the value of
`value`

is NULL, then the result of the function is NULL.## DTAN

Returns the tangent for a numeric expression.

## Syntax

`value`

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

- The function returns the tangent for the value expressed as radians. The data type for the result is double precision.
- If the value of
`value`

is NULL, then the result of the function is NULL.## 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 integer.
The exception is when

`field-name`

is`SECOND`

, in which case the result type is decimal where 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'## 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

- The function returns the nearest integer value that is equal or lower to
`value`

.- If the value of
`value`

is`NULL`

, then the result of the function is`NULL`

.- The return data type is based on the input data type. For DECIMAL input, the return data type is integer.
## 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

- The result is a character value.
- If the value of
`value`

is`NULL`

, then the result of the function is`NULL`

.- The
`INDEX_CHAR`

function takes a character string as argument and returns the index character for the string related to it's collation. The default behavior is to return the first letter of the string, decomposed (accents removed) and capitalized (upper case).However, many languages include accented letters, digraphs, and sometimes trigraphs as basic alphabetical characters. These combinations are properly handled by the

`INDEX_CHAR`

function.## 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

- The result is a random integer value, in the range 0 to 2 147 483 647.
- 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## 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

- The result is the current time (i.e. now) as a
`TIME`

value.- The value of
`seconds-precision`

must be between 0 and 9.- If
`seconds-precision`

is not specified, the default value of 0 is assumed.- All references to
`LOCALTIME`

are effectively evaluated simultaneously from a single reading of the server clock. Thus the conditional expression`LOCALTIME = LOCALTIME`

is guaranteed to always evaluate to true.- The value of
`LOCALTIME`

will always be equal to the`TIME`

portion of`LOCALTIMESTAMP`

.## 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

- The result is the current date and time as a
`TIMESTAMP`

value.- The value of
`seconds-precision`

must be between 0 and 9.- If
`seconds-precision`

is not specified, the default value of 6 is assumed.- All references to
`LOCALTIMESTAMP`

are effectively evaluated simultaneously from a single reading of the server clock. Thus the conditional expression`LOCALTIMESTAMP = LOCALTIMESTAMP`

is guaranteed to always evaluate to true.- The value of
`LOCALTIMESTAMP`

will always be equal to the combined value of`CURRENT_DATE`

and`LOCALTIME`

.## 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

- The data type of the result is the same as the data type of
`source-string`

.`source-string`

is either in character or national character (i.e. Unicode) format.- If the value of
`source-string`

is`NULL`

, then the result of the function is`NULL`

.## 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

`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 the 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 session' current value for the sequence).- If the sequence is unique (i.e.
`NO CYCLE`

option) 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 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

`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 data type of
`source-string`

is variable-length national character, then the result of`OCTET_LENGTH`

is the same as the actual length of`source-string`

in octets, i.e. 4 times the actual number of characters.- If the data type of
`source-string`

is fixed-length national character, then the result of`OCTET_LENGTH`

is the same as 4 times 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 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

- The
`string-length`

number of characters in`string-1`

, starting from position`start-position`

are deleted from`string-1`

. Then`string-2`

is inserted into`string-1`

, at the `point of deletion'. The resulting character or binary string is returned.- If the value of
`string-length`

is positive, the`string-length`

number of characters to the right of`start-position`

are deleted. If the value of`string-length`

is negative, the`string-length`

number of characters to the left of`start-position`

are deleted.The point-of-deletion is where the cursor would be if you had just used a text editor to select the characters, as described, and performed an edit-cut operation.

- A value for
`start-position`

of less than 1 (zero or negative) specifies a position to the left of the beginning of`string-1`

.It is possible that the specified deletion may not actually affect any of the characters of

`string-1`

, in which case the paste operation produces the effect of a prepend.- If the value of any operand is
`NULL`

, then the result of the function is`NULL`

.`string-2`

must not contain Unicode characters outside the Latin1 repertoire if`string-1`

is of character type.## 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

- 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## 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

- The result is a character or binary string consisting of
`sub-string`

repeated`repeat-count`

times.- If the value of
`repeat-count`

is zero, then the result of the function is a character or binary string of length zero.- If the value of
`repeat-count`

is less than zero, then the result of the function is`NULL`

.- If the value of either operand is
`NULL`

, then the result of the function is`NULL`

.## 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

- All occurrences of
`string-1`

found in`source-string`

are replaced with`string-2`

, the resulting character or binary string is returned.- If the value of any of the operands is
`NULL`

, then the result of the function is`NULL`

.`string-2`

must not contain Unicode characters outside the Latin1 repertoire if`source-string`

is of character type.## 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

- 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## SESSION_USER

Returns the name of the currently connected ident.## Syntax

Syntax for the

`SESSION_USER`

function:## Rules

- The result is the name of the current ident (i.e. the ident who established the current database connection).
- The data type of the returned value is nchar varying with a maximum length of 128, with the collation SQL_IDENTIFIER.
## 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

`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## 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

- The function returns a character string value containing six digits that represent an encoding of the sound of
`source-string`

.- If
`source-string`

contains two or more words, they are effectively concatenated into a single word by ignoring the separating space characters.- If the
`SOUNDEX`

values for two strings compare to be equal then they sound the same.- If the value of
`source-string`

is`NULL`

, then the result of the function is`NULL`

.## 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

`SUBSTRING`

returns a character or binary string containing`string-length`

characters of`source-string`

, starting at the character specified by`start-position`

, and in the same sequence as they appear in`source-string`

.If any of these positions are before the start or after the end of

`source-string`

, then no character is returned for that position. If all positions are outside the source string, an empty string is returned.- The first character in
`source-string`

has position 1.- If the data type of
`source-string`

is variable-length character, then the result of the`SUBSTRING`

function is a variable-length character with maximum string length equal to the maximum length of`source-string`

. If the data type of`source-string`

is fixed-length character, then the result of the`SUBSTRING`

function is a variable-length character with maximum string length equal to the fixed length of`source-string`

.- If the data type of
`source-string`

is variable-length binary, then the result of the`SUBSTRING`

function is a variable-length binary with maximum string length equal to the maximum length of`source-string`

. If the data type of`source-string`

is fixed-length binary, then the result of the`SUBSTRING`

function is a variable-length binary with maximum string length equal to the fixed length of`source-string`

.- If
`string-length`

is negative, or if`start-position`

is greater than the number of characters in`source-string`

, the function fails and an error is returned.- If
`string-length`

is omitted then it is assumed to be:CHAR_LENGTH(source-string) + 1 - start-positioni.e. the remainder of

`source-string`

, starting at`start-position`

, is returned.

- If the value of any operand is
`NULL`

, then the result of the function is`NULL`

.- Character strings returned from a
`SUBSTRING`

function, inherit the collation from the source string.## 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

`source-string`

is a character or binary string expression.

`count`

is an integer value expression.## Rules

- The right-most
`count`

characters of`source-string`

are returned.- If
`count`

is zero, an empty string is returned.- If
`count`

is less than zero, then the result of the function is`NULL`

.- If the value of either operand is
`NULL`

, then the result of the function is`NULL`

.## 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

`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

- If
`trim-character`

is not specified, '`' (space) is implicit for character data, and`

`x'00'`

is implicit for binary data.- If
`trim-specification`

is not specified,`BOTH`

is implicit.- If the data type of
`source-string`

is variable-length character, then the result of the`TRIM`

function is a variable-length character with maximum string length equal to the maximum length of`source-string`

. If the data type of`source-string`

is fixed-length character, then the result of the`TRIM`

function is a variable-length character with maximum string length equal to the length of`source-string`

.- If the data type of
`source-string`

is variable-length binary, then the result of the`TRIM`

function is a variable-length binary with maximum string length equal to the maximum length of`source-string`

. If the data type of`source-string`

is fixed-length binary, then the result of the`TRIM`

function is a variable-length binary with maximum string length equal to the length of`source-string`

.- If the length of
`trim-character`

is not 1, an error is returned.- If the value of either operand is
`NULL`

, then the result of the function is`NULL`

.- Character strings returned from a
`TRIM`

function, inherit the collation from the source string.## 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

- 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## 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

- If the value of
`code`

represents a valid Unicode character, the function returns a single national character value, i.e.`NCHAR(1)`

, otherwise an error is raised.- If the value of
`code`

is`NULL`

, then the result of the function is`NULL`

.## 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

- A single
`INTEGER`

value is returned, representing a Unicode scalar value.- If the
`source-string`

contains more than one character, the Unicode scalar value of the left-most character 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 = 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

- The data type of the result is the same as the data type of
`source-string`

.`source-string`

is either in character or national character (i.e. Unicode) format.- If the value of
`source-string`

is`NULL`

, then the result of the function is`NULL`

.## 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

## 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. (The year's first week with 4 or more days is week 1.)
- 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

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se |