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

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

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

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 lower to value.
• If the value of value is NULL, then the result of the function is NULL.

Example

 SET :i = CEILING(3.57);  -- sets :i to 4

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

 SET D = 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 character varying value with a maximum length of 128.
• 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 or OS_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 character varying with a maximum length of 128.

Example

 CREATE DOMAIN NAME AS CHAR(128)
 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 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;

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

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

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

• 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

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

• 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

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

• 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

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

• The function returns the natural logarithm 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.

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.
• 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 or integer-value is NULL, then the result of the function is NULL.

DRADIAN

Returns an angle expressed in degrees as radians.

Syntax

Syntax for the DRADIAN 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

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

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

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

• 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 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'

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 higher to value.
• If the value of value is NULL, then the result of the function is NULL.

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

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. 2003-01-18 13:25: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 assumed to be 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

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

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 Latin 1 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 Latin 1 repertoire if source-string is of character type.

Example

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

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 character varying with a maximum length of 128.

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

• 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-position
 

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

Syntax for the TAIL function:

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

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

• 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 trim-specification is not specified, BOTH is implicit.
• If trim-character is not specified, ' ' (space) is implicit.
• 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('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 assumed to be 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

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

• 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

                                                     -- 'TENOR NORRING'

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