2.16.2 System-defined scalar functions
(1) ACOS
(a) Function
Returns in the range 0 to
the angle (in radians) that is the inverse cosine of an argument.
(b) Format
[MASTER.]ACOS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- Specifying in the argument a value whose function value is not defined causes a definition area error (domain error occurs).
(2) ADD_INTERVAL
(a) Function
Returns the predefined character string representation of a time stamp, which is obtained by adding the datetime interval (
YYYYMMDDhhmmss.) in decimal representation specified in argument-2 to the time stamp in predefined character string representation ('YYYY-MM-DD hh:mm:ss') specified in argument-1.
(b) Format
[MASTER.]ADD_INTERVAL(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression of argument-2:
- Decimal or integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a character string data type (CHAR or VARCHAR), and its length must not exceed 19 bytes.
- The data type of argument-2 must be DECIMAL, INTEGER, or SMALLINT. An integer value must not exceed 14 digits (the decimal point is ignored if specified).
- The data type of the result will be CHAR(19).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- As the value of argument-1, specify the predefined character string representation ('YYYY-MM-DD hh:mm:ss') of a valid time stamp. A fractional second should not be used in the predefined character string representation of a time stamp.
- argument-2 must specify in the format
YYYYMMDDhhmmss. the decimal representation of a date and time interval:
- YYYY: Years
- MM: Months
- DD: Days
- hh: Hours
- mi: Minutes
- ss: Seconds
If the value of argument-2 is positive, the operation adds a datetime interval (YYYYMMDDhhmmss.) to the time stamp specified in argument-1. If the value of argument-2 is negative, the operation results in the subtraction of the datetime interval (YYYYMMDDhhmmss.) from the time stamp specified in argument-1.
- The date and time interval is added or subtracted in the following order: years, months, days, hours, minutes, seconds. If the result falls on a non-existent date (the 31st of a month with fewer than 31 days or on February 29 of a non-leap year), the result is adjusted to the last day of the month.
Adding months to the last day of a month does not necessarily produce the last day of the result month. Also, adding a number of months to a date and subtracting the same number of months from the resulting date does not necessarily produce the original date.
- If the result of the ADD_INTERVAL function is not within the range 0001/01/01 00:00:00 - 9999/12/31 23:59:59, an overflow error occurs. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
- The result of the function is the predefined character string representation of a time stamp.
(d) Examples
Examples of the ADD_INTERVAL function are shown below:
ADD_INTERVAL('1999-12-31 23:59:59',10000000000001.)
==> '2001-01-01 00:00:00'
ADD_INTERVAL(2001-01-01 00:00:00',-10000000000001.)
==> '1999-12-31 23:59:59'
ADD_INTERVAL('1956-06-07 03:15:30',400313115450.)
==> '1996-09-20 15:10:20'
ADD_INTERVAL('1998-12-31 13:59:59',10200030405.)
==> '2000-02-29 17:04:04'
ADD_INTERVAL('2000-02-29 17:04:04',-10200030405.)
==> '1998-12-28 13:59:59'
(3) ASCII
(a) Function
Returns the ASCII integer value for the first character in the character string specified in an argument.
(b) Format
[MASTER.]ASCII(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR).
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the length of the argument value is 0 bytes, the result will be the null value.
(d) Example
An example of the ASCII function is shown below:
ASCII('ABC') ==> 65
CHR(ASCII('ABC')) ==> 'A'
(4) ASIN
(a) Function
Returns in the range -
/2 to
/2 the angle (in radians) that is the inverse sine of an argument.
(b) Format
[MASTER.]ASIN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- Specifying in the argument a value whose function value is not defined causes a definition area error (domain error occurs).
(5) ATAN
(a) Function
Returns in the range -
/2 to
/2 the angle (in radians) that is the inverse tangent of an argument.
(b) Format
[MASTER.]ATAN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(6) ATAN2
(a) Function
Returns in the range -
to
the angle (in radians) that is the inverse tangent of x/y, where x is argument-1 and y is argument-2.
(b) Format
[MASTER.]ATAN2(argument-1, argument-2)
(c) Rules
- The following items can be specified in argument-1 and argument-2:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
(7) CEIL
(a) Function
Returns the smallest integer that is equal to or greater than the value of an argument.
(b) Format
[MASTER.]CEIL(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- Table 2-40 shows the data type of the result.
Table 2-40 Data type of the result of a CEIL system-defined scalar function
Data type of argument | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the CEIL function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(8) CENTURY
(a) Function
Returns as an ordinal number the century of the date specified in an argument.
(b) Format
[MASTER.]CENTURY(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the CENTURY function are shown below:
CENTURY(DATE'1900-12-31')) ==> 19
CENTURY(DATE'1901-01-01')) ==> 20
CENTURY(DATE'1999-12-31')) ==> 20
CENTURY(DATE'2000-12-31')) ==> 20
CENTURY(DATE'2001-01-01')) ==> 21
(9) CHR
(a) Function
Returns the ASCII character represented by the integer value specified in an argument (returns the null value if the argument value is not in the range 0 to 255).
(b) Format
[MASTER.]CHR(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be INTEGER or SMALLINT.
- The data type of the result will be CHAR(1).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Example
An example of the CHR function is shown below:
CHR(65 ==> 'A'
ASCII(CHR(65)) ==> 65
(10) COS
(a) Function
Returns the cosine (COS trigonometric function) of an argument that is an angle specified in radians.
(b) Format
[MASTER.]COS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(11) COSH
(a) Function
Returns the hyperbolic cosine of an argument.
(b) Format
[MASTER.]COSH(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the COSH function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(12) DATE_TIME
(a) Function
Returns the date of the DATE type specified in argument-1 and the time of the TIME type specified in argument-2 by converting them into the predefined character string representation ('YYYY-MM-DD hh:mm:ss') of a time stamp.
(b) Format
[MASTER.]DATE_TIME(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression of argument-2:
- CURRENT_TIME
- Column specification
- SQL variable or SQL parameter
- Time operations
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE; the data type of argument-2 must be TIME.
- The data type of the result will be CHAR(19).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The predefined character string representation of the time stamp, which is the result of DATE_TIME(date, time), is a value that is obtained by concatenation of the predefined character string representation of date, one space character, and the predefined character string representation of time.
CHAR(date) | | ' ' | | CHAR(time)
(d) Examples
Examples of the DATE_TIME function are shown below:
DATE_TIME(DATE('1999-12-31'), TIME('23:59:59'))
==> '1999-12-31 23:59:59'
DATE_TIME(CURRENT_DATE, CURRENT_TIME)
==> '1999-07-27 11:05:20'
(13) DAYNAME
(a) Function
Returns the character string (such as "Sunday" or "Monday") for the day of the week of the date specified in an argument.
(b) Format
[MASTER.]DAYNAME(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be VARCHAR(18).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the DAYNAME function are shown below:
DAYNAME(DATE('1999-06-06')) ==> 'Sunday'
DAYNAME(DATE('1999-06-07')) ==> 'Monday'
DAYNAME(DATE('1999-06-08')) ==> 'Tuesday'
DAYNAME(DATE('1999-06-09')) ==> 'Wednesday'
DAYNAME(DATE('1999-06-10')) ==> 'Thursday'
DAYNAME(DATE('1999-06-11')) ==> 'Friday'
DAYNAME(DATE('1999-06-12')) ==> 'Saturday'
(14) DAYOFWEEK
(a) Function
Returns the integer indicating the day of the week ("1" for Sunday,"2" for Monday, etc.) for the date specified in an argument.
(b) Format
[MASTER.]DAYOFWEEK(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the DAYOFWEEK function are shown below:
DAYOFWEEK(DATE('1999-06-06')) ==> 1
DAYOFWEEK(DATE('1999-06-07')) ==> 2
DAYOFWEEK(DATE('1999-06-08')) ==> 3
DAYOFWEEK(DATE('1999-06-11')) ==> 6
DAYOFWEEK(DATE('1999-06-12')) ==> 7
'1999-06-06' was a Sunday.
(15) DAYOFYEAR
(a) Function
Returns the integer (in the range 1 to 366) that represents the date specified in an argument as the number of days elapsed since January 1.
(b) Format
[MASTER.]DAYOFYEAR(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the DAYOFYEAR function are shown below:
DAYOFYEAR(DATE('1999-01-01')) ==> 1
DAYOFYEAR(DATE('1999-02-28')) ==> 53
DAYOFYEAR(DATE('1999-06-07')) ==> 158
DAYOFYEAR(DATE('1999-12-31')) ==> 365
DAYOFYEAR(DATE('2000-12-31')) ==> 366
(16) DEGREES
(a) Function
Converts to degrees the angle specified in radians in an argument.
(b) Format
[MASTER.]DEGREES(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the DEGREES function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(17) EXP
(a) Function
Determines an exponent to the base of the natural logarithm.
(b) Format
[MASTER.]EXP(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the EXP function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(18) FLOOR
(a) Function
Returns the largest integer that is equal to or less than the value of an argument.
(b) Format
[MASTER.]FLOOR(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- Table 2-41 shows the data type of the result.
Table 2-41 Data type of the result of a FLOOR system-defined scalar function
Data type of argument | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the FLOOR function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(19) GREATEST
(a) Function
Returns the largest value among specified arguments.
(b) Format
[MASTER.]GREATEST(argument, argument[, argument])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- A maximum of three arguments can be specified.
- The following can be specified in each value expression:
- Literal
- USER, CURRENT_DATE, or CURRENT_TIME
- Column specification
- SQL variable or SQL parameter
- Arithmetic, date, time, or concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- All specified arguments must be of one of the following data types: numeric, character string, mixed character string, national character string, date, or time.
- Table 2-42 shows the data type of the result.
Table 2-42 Data type of the result of a GREATEST system-defined scalar function
Data type of integer | Data type of result |
---|
INTEGER or SMALLINT | INTEGER |
DECIMAL [, INTEGER or SMALLINT] | DECIMAL1 |
FLOAT, SMALLFLT [, DECIMAL, INTEGER, or SMALLINT] | FLOAT |
CHAR or VARCHAR | VARCHAR2 |
MCHAR or MVARCHAR | MVARCHAR2 |
NCHAR or NVARCHAR | NVARCHAR2 |
DATE | DATE |
TIME | TIME |
- 1 The following precision and scaling apply, where pi and si denote the precision and scaling of the ith argument, respectively:
- Precision = max(p1-s1, p2-s2, ...) + max(s1, s2, ...)
- Scaling = max(s1, s2, ...)
- A result whose precision exceeds 29 causes an error.
- INTEGER is treated as DECIMAL(10,0); SMALLINT is treated as DECIMAL(5,0).
- 2 The following maximum length applies, where ni denotes the maximum length of the ith argument (the definition length in the case of a fixed-length data type):
- Maximum length = max(n1, n2, ...)
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
(20) HALF
(a) Function
Based on a month specified in argument-2 and a day specified in argument-3 as the beginning of the fiscal year, returns an integer (1 or 2) indicating whether the date specified in argument-1 is in the first half or the second half of the fiscal year.
(b) Format
[MASTER.]HALF(argument-1 [, argument-2 [, argument-3]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expressions of argument-2 and argument-3:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE; the data types of argument-2 and argument-3 must be INTEGER or SMALLINT.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- For argument-2, the default is 1 and the range of specifiable values is 1 to 12. For argument-3, the default is 1 and the range of specifiable values is 1 to 29 when the value of argument-2 is 2 and 1 to (number of days in the month specified in argument-2) in all other cases.
- The result is determined according to the following rules:
- If day of argument-1-date < argument-3-day:
Number of months = month of argument-1 date - argument-2-month - 1
- If day of argument-1-date
argument-3-day:
Number of months = month of argument-1 date - argument-2-month
- If number of months < 0:
Result = (number of months + 12)
2 + 1
- If number of months
0:
Result = number of months
2 + 1
(d) Examples
Examples of the HALF function are shown below:
HALF(DATE('1999-01-01')) ==> 1
HALF(DATE('1999-09-10')) ==> 2
HALF(DATE('1999-12-31')) ==> 2
HALF(DATE('1999-04-01'), 4) ==> 1
HALF(DATE('1999-09-10'), 4) ==> 1
HALF(DATE('1999-03-31'), 4) ==> 2
HALF(DATE('1999-03-21'), 3, 21) ==> 1
HALF(DATE('1999-09-20'), 3, 21) ==> 1
HALF(DATE('1999-03-20'), 3, 21) ==> 2
(21) INSERTSTR (INSERTSTR_LONG)
(a) Function
Deletes from the character string specified in argument-1 the substring beginning at the character position specified in argument-2 and consisting of the number of characters specified in argument-3, then sets the character string specified in argument-4 at the argument-2 position and returns the modified character string.
(b) Format
[MASTER.]INSERTSTR(argument-1, argument-2, argument-3, argument-4)
[MASTER.]INSERTSTR_LONG(argument-1, argument-2, argument-3, argument-4)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expressions of argument-1 and argument-4:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expressions of argument-2 and argument-3:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-4 must both be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR).
- Tables 2-43 and 2-44 show the maximum length for the value of argument-4.
Table 2-43 Maximum length of argument-4 of the INSERTSTR system-defined scalar function
Data type of argument-1 (and argument-4) | Maximum length of argument-4 |
---|
CHAR or VARCHAR | 255 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
Table 2-44 Maximum length of argument-4 of the INSERTSTR_LONG system-defined scalar function
Data type of argument-1 (and argument-4) | Maximum length of argument-4 |
---|
CHAR or VARCHAR | 32000 |
MCHAR or MVARCHAR | 32000 |
NCHAR or NVARCHAR | 16000 |
- The data types of argument-2 and argument-3 must be INTEGER or SMALLINT.
- Specifying 0 in argument-3 means that nothing is to be deleted.
- Tables 2-45 and 2-46 show the data type of the result.
Table 2-45 Data type of the result of the INSERTSTR system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
Table 2-46 Data type of the result of the INSERTSTR_LONG system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(32000) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(32000) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(16000) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- Tables 2-47 and 2-48 show the ranges of values that can be specified in argument-2 and argument-3, where m denotes the value of argument-2.
Table 2-47 Ranges of specifiable values in argument-2 and argument-3 of the INSERTSTR system-defined scalar function
Data type of argument-1 | Range of values in argument-2 | Range of values in argument-3 |
---|
CHAR(n) or VARCHAR(n) | 1 to n | 0 to (n + 1 - m) |
MCHAR(n) or MVARCHAR(n) | 1 to n | 0 to (n + 1 - m) |
NCHAR(n) or NVARCHAR(n) | 1 to n | 0 to (n + 1 - m) |
Table 2-48 Ranges of specifiable values in argument-2 and argument-3 of the INSERTSTR_LONG system-defined scalar function
Data type of argument-1 | Range of value of argument-2 | Range of value of argument-3 |
---|
CHAR(n) or VARCHAR(n) | 1 to 32000 | 0 to (32001-m) |
MCHAR(n) or MVARCHAR(n) | 1 to 32000 | 0 to (32001-m) |
NCHAR(n) or NVARCHAR(n) | 1 to 16000 | 0 to (16001-m) |
- If the character string specified in argument-1 is shorter than the character position (m) specified in argument-2, the character string is padded with spaces until its length is (m-1); there is no deletion of a substring in such a case (when the data type of argument-1 is NCHAR or NVARCHAR, the padding character is the double-byte space).
If the character string specified in argument-1 is longer than m but less than (m-1+nd), the characters beginning at position m through the last character are deleted (nd is the number of characters specified in argument-3).
- The length of the result must not exceed the maximum length for the result data type. When the length of the result will exceed the length of the argument-1 character string, the INSERTSTR_LONG function should be used.
(d) Examples
Examples of the INSERTSTR and INSERTSTR_LONG functions are shown below:
INSERTSTR('data warehouse system', 6, 9, 'base')
==> 'data base system'
INSERTSTR_LONG('data system', 6, 0, 'warehouse ')
==> 'data warehouse system'
INSRTSTR('data base management system', 11, 11, '')
==> 'data base system'
The character string specified in argument-4 has a length 0.
INSERTSTR_LONG('data base system', 31, 0, '')
==> 'data base system '
The result value contains 14 space characters following the string system.
(22) INTERVAL_DATETIMES
(a) Function
Returns the datetime interval between two time stamps in predefined character string representation ('YYYY-MM-DD hh:mm:ss') specified in the arguments, in terms of a decimal representation (
YYYYMMDDhhmmss.). If time-stamp-of-argument-1 < time-stamp-of-argument-2, the result is a negative value.
(b) Format
[MASTER.]INTERVAL_DATETIMES(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be a character string data type (CHAR or VARCHAR), and the length of the arguments must be 19 bytes.
- The data type of the result will be DECIMAL(14).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- For the value of argument-1 and argument-2, specify a valid predefined character string representation ('YYYY-MM-DD hh:mm:ss') of the time stamp; do not specify a fractional second in the predefined character string representation of the time stamp.
- The result of INTERVAL_DATETIMES(time-stamp-1, time-stamp-2) is calculated according to the following rules:
- If time-stamp-1 < time-stamp-2:
Result = -INTERVAL_DATETIMES(time-stamp-1, time-stamp-2)
- If time-stamp-1
time-stamp-2:
If second of time-stamp-1
second of time-stamp-2:
Second of the result = second of time-stamp-1 - second of time-stamp-2
If second of time-stamp-1 < second of time-stamp-2:
Second of the result = second of time-stamp-1 - second of time-stamp-2 + 60
Minute of time-stamp-2 = minute of time-stamp-2 + 1
If minute of time-stamp-1
minute of time-stamp-2:
Minute of the result = minute of time-stamp-1 - minute of time-stamp-2
If minute of time-stamp-1 < minute of time-stamp-2:
Minute of the result = minute of time-stamp-1 - minute of time-stamp-2 + 60
Hour of time-stamp-2 = hour of time-stamp-2 + 1
If hour of time-stamp-1
hour of time-stamp-2:
Hour of the result = hour of time-stamp-1 - hour of time-stamp-2
If hour of time-stamp-1 < hour of time-stamp-2:
Hour of the result = hour of time-stamp-1 - hour of time-stamp-2 + 24
Day of time-stamp-2 = day of time-stamp-2 + 1
If day of time-stamp-1
day of time-stamp-2:
Day of the result = day of time-stamp-1 - day of time-stamp-2
If day of time-stamp-1 < day of time-stamp-2
Day of the result = day of time-stamp-1 - day of time-stamp-2 + last day of the month of time-stamp-2
Month of time-stamp-2 = month of time-stamp-2 + 1
If month of time-stamp-1 = month of time-stamp-2:
Month of the result = month of time-stamp-1 - month of time-stamp-2
If month of time-stamp-1 < month of time-stamp-2:
Month of the result = month of time-stamp-1 - month of time-stamp-2 + 12
Year of time-stamp-2 = year of time-stamp-2 + 1
Year of the result = year of time-stamp-1 - year of time-stamp-2
- Result = (years of result x 10000000000 + months of result x 100000000 + days of result x 1000000 + hours of result x 10000 + minutes of result x 100 + seconds of result)
(d) Examples
Examples of the INTERVAL_DATETIMES function are shown below:
INTERVAL_DATETIMES('2001-01-01 00:00:00',
'1999-12-31 23:59:59')
==> 10000000001.
INTERVAL_DATETIMES('1999-12-31 23:59:59',
'2001-01-01 00:00:00')
==> -10000000001.
INTERVAL_DATETIMES('1996-09-20 15:10:20',
'1956-06-07 03:15:30')
==> 400313115450.
NUMEDIT(INTERVAL_DATETIMES('1996-09-20 15:10:20',
'1956-06-07 03:15:30'),
'<9990" YEARS "90" MONTHS "90" DAYS "90" HOURS "
90" MINUTES "90" SECONDS"')
==> '40 years 3 months 13 days 11 hours 54 minutes 50 seconds'
(23) ISDIGITS
(a) Function
Returns a BOOLEAN value indicating whether or not the character string specified in an argument is composed solely of numeric digits.
(b) Format
[MASTER.]ISDIGITS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal or mixed character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR) or mixed character string data type (MCHAR or MVARCHAR) and its length may not exceed 60 bytes.
- The data type of the result will be BOOLEAN.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the character string specified in the argument consists solely of numeric digits (0 to 9), the result is TRUE; otherwise, the result is FALSE.
(d) Examples
Examples of the ISDIGITS function are shown below:
ISDIGITS('1234567890') ==> true
ISDIGITS('123ABC') ==> false
ISDIGITS('') ==> false
(24) IS_DBLBYTES
(a) Function
Returns a BOOLEAN value indicating whether or not the character string specified in an argument is composed solely of double-byte characters.
(b) Format
[MASTER.]IS_DBLBYTES(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal or mixed character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR) or mixed character string data type (MCHAR or MVARCHAR).
- The data type of the result will be BOOLEAN.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the character string specified in the argument consists solely of double-byte characters, the result is TRUE; otherwise, the result is FALSE.
(d) Examples
Examples of the IS_DBLBYTES function are shown below:
![[Figure]](figure/zu2s030a.gif)
(25) IS_SNGLBYTES
(a) Function
Returns a BOOLEAN value indicating whether or not the character string specified in an argument is composed solely of single-byte characters.
(b) Format
[MASTER.]IS_SNGLBYTES(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal or mixed character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CAST specification
- CASE expression
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR) or mixed character string data type (MCHAR or MVARCHAR).
- The data type of the result will be BOOLEAN.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the character string specified in the argument consists solely of single-byte characters, the result is TRUE; otherwise, the result is FALSE.
(d) Examples
Examples of the IS_SNGLBYTES function are shown below:
![[Figure]](figure/zu2s030b.gif)
(26) LAST_DAY
(a) Function
Returns the last day of the month for the date specified in an argument.
(b) Format
[MASTER.]LAST_DAY(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be DATE.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the LAST_DAY function are shown below:
LAST_DAY(DATE('1999-01-01')) ==> '1999-01-31'
LAST_DAY(DATE('1999-02-16')) ==> '1999-02-28'
LAST_DAY(DATE('1999-06-10')) ==> '1999-06-30'
LAST_DAY(DATE('1999-12-25')) ==> '1999-12-31'
LAST_DAY(DATE('2000-02-03')) ==> '2000-02-29'
(27) LEAST
(a) Function
Returns the smallest value among specified arguments.
(b) Format
[MASTER.]LEAST(argument, argument [, argument])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- A maximum of three arguments can be specified.
- The following can be specified in each value expression:
- Literal
- USER, CURRENT_DATE, or CURRENT_TIME
- Column specification
- SQL variable or SQL parameter
- Arithmetic, date, time, or concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- All specified arguments must be of one of the following data types: numeric, character string, mixed character string, national character string, date, or time.
- Table 2-49 shows the data type of the result.
Table 2-49 Data type of the result of the LEAST system-defined scalar function
Data type of integer | Data type of result |
---|
INTEGER or SMALLINT | INTEGER |
DECIMAL [, INTEGER or SMALLINT] | DECIMAL1 |
FLOAT, SMALLFLT [, DECIMAL, INTEGER, or SMALLINT] | FLOAT |
CHAR or VARCHAR | VARCHAR2 |
MCHAR or MVARCHAR | MVARCHAR2 |
NCHAR or NVARCHAR | NVARCHAR2 |
DATE | DATE |
TIME | TIME |
- 1 The following precision and scaling apply, where pi and si denote the precision and scaling of the ith argument, respectively:
- Precision = max(p1 - s1, p2 - s2, ...) + max(s1, s2, ...)
- Scaling = max(s1, s2, ...)
- A result whose precision exceeds 29 causes an error.
- INTEGER is treated as DECIMAL(10,0); SMALLINT is treated as DECIMAL(5,0).
- 2 The following maximum length applies, where ni denotes the maximum length of the ith argument (the definition length in the case of a fixed-length data type):
- Maximum length = max(n1, n2, ...)
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
(28) LEFTSTR
(a) Function
Returns from the beginning (the leftmost position) of the character string specified in argument-1 the substring consisting of the number of characters specified in argument-2.
(b) Format
[MASTER.]LEFTSTR(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression of argument-2:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string type data type (NCHAR or NVARCHAR).
- The data type of argument-2 must be INTEGER or SMALLINT.
- Table 2-50 shows the range of values that can be specified in argument-2.
Table 2-50 Range of specifiable values in argument-2 of the LEFTSTR system-defined scalar function
Data type of argument-1 | Range of values in argument-2 |
---|
CHAR(n) or VARCHAR(n) | 0 to n |
MCHAR(n) or MVARCHAR(n) | 0 to n |
NCHAR(n) or NVARCHAR(n) | 0 to n |
- Table 2-51 shows the data type of the result.
Table 2-51 Data type of the result of the LEFTSTR system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- If the number of characters in the argument-1 character string is less than the number of characters specified in argument-2, the result will be the argument-1 value.
(d) Examples
Examples of the LEFTSTR function are shown below:
LEFTSTR('data base system', 9) ==> 'data base'
LEFTSTR('DATA SYSTEM', 0) ==> ''(Character string of 0 length)
(29) LN
(a) Function
Returns the natural logarithm of an argument.
(b) Format
[MASTER.]LN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(30) LOG10
(a) Function
Returns the common logarithm of an argument.
(b) Format
[MASTER.]LOG10(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(31) LTRIM
(a) Function
Beginning at the left end of the character string specified in argument-1, removes all instances of each of the characters in the character string specified in argument-2 until a character not found in the argument-2 character string is encountered.
(b) Format
[MASTER.]LTRIM(argument-1 [, argument-2])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR). The maximum length of the argument-2 value is 30 bytes for a character string or mixed character string data type and 30 characters for a national character string data type.
- Table 2-52 shows the data type of the result.
Table 2-52 Data type of the result of the LTRIM system-defined scalar function
Data type of argument-1 (and argument-2) | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- If argument-2 is omitted, one space character in the data type of the result is assumed.
(d) Examples
Examples of the LTRIM function are shown below:
LTRIM('abcabcabdata base', 'abc') ==> 'abdata base'
LTRIM(' data base') ==> 'data base'
(32) LTRIMSTR
(a) Function
Beginning at the left end of the character string specified in argument-1, deletes each successive occurrence of the character string specified in argument-2 until it does not find that character string.
(b) Format
[MASTER.]LTRIMSTR(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR).
- Table 2-53 shows the maximum length for the value of argument-2.
Table 2-53 Maximum length of argument-2 of the LTRIMSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Maximum length of argument-2 |
---|
CHAR or VARCHAR | 255 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
- Table 2-54 shows the data type of the result.
Table 2-54 Data type of the result of the LTRIMSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
(d) Example
An example of the LTRIMSTR function is shown below:
LTRIMSTR('abcabcabdata base', 'abc') ==> 'abdata base'
(33) MIDNIGHTSECONDS
(a) Function
Returns the number of seconds from midnight to the time specified in an argument.
(b) Format
[MASTER.]MIDNIGHTSECONDS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Time operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be TIME.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- The result can be obtained from the following formula:
Result = (((hour of argument x 60) + minute of argument) x 60) + second of argument
(d) Examples
Examples of the MIDNIGHTSECONDS function are shown below:
MIDNIGHTSECONDS(TIME('23:59:59')) ==> 86399
MIDNIGHTSECONDS(TIME('14:14:14')) ==> 51254
(34) MONTHNAME
(a) Function
Returns the month name (such as "January" or "February") of the date specified in an argument.
(b) Format
[MASTER.]MONTHNAME(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be VARCHAR(18).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the MONTHNAME function are shown below:
MONTHNAME(DATE('1999-01-01')) ==> 'January'
MONTHNAME(DATE('1999-02-28')) ==> 'February'
MONTHNAME(DATE('1999-03-03')) ==> 'March'
MONTHNAME(DATE('1999-04-01')) ==> 'April'
MONTHNAME(DATE('1999-05-05')) ==> 'May'
MONTHNAME(DATE('1999-06-07')) ==> 'June'
MONTHNAME(DATE('1999-07-07')) ==> 'July'
MONTHNAME(DATE('1999-08-15')) ==> 'August'
MONTHNAME(DATE('1999-09-23')) ==> 'September'
MONTHNAME(DATE('1999-10-10')) ==> 'October'
MONTHNAME(DATE('1999-11-11')) ==> 'November'
MONTHNAME(DATE('1999-12-31')) ==> 'December'
(35) MONTHS_BETWEEN
(a) Function
Returns as a real number (FLOAT type) the number of months between two dates specified in arguments.
(b) Format
[MASTER.]MONTHS_BETWEEN(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data types of argument-1and argument-2 must be DATE.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The number of months which is the result of MONTHS_BETWEEN is calculated according to the following rules:
(d) Examples
Examples of the MONTHS_BETWEEN function are shown below:
MONTHS_BETWEEN(DATE('1999-07-10'), DATE('1999-06-10'))
==> 1
MONTHS_BETWEEN(DATE('1999-07-11'), DATA('1999-06-10'))
==> 1.032258...
MONTHS_BETWEEN(DATE('1999-06-11'), DATA('1999-05-10'))
==> 1.033333...
MONTHS_BETWEEN(DATE('1999-02-11'), DATA('1999-01-10'))
==> 1.035714...
MONTHS_BETWEEN(DATE('2000-02-11'), DATA('2000-01-10'))
==> 1.034482...
MONTHS_BETWEEN(DATE('1999-09-09'), DATA('1999-06-10'))
==> 2.967741...
MONTHS_BETWEEN(DATE('1999-06-10'), DATA('1999-09-09'))
==> -2.967741...
(36) NEXT_DAY
(a) Function
Returns the next date after the date specified in argument-1 that is the same day of the week as the weekday number specified in argument-2 (where Sunday is weekday 1).
(b) Format
[MASTER.]NEXT_DAY(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression for argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression for argument-2:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE; the data type of argument-2 must be INTEGER or SMALLINT.
- The data type of the result will be DATE.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- Table 2-55 shows the meanings of the integer values that can be specified in argument-2.
Table 2-55 Meanings of integer values specifiable in argument-2 (integer values for days of the week)
Integer value of argument-2 | Day of week |
---|
1 | Sunday |
2 | Monday |
3 | Tuesday |
4 | Wednesday |
5 | Thursday |
6 | Friday |
7 | Saturday |
- If the NEXT_DAY function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(d) Examples
Examples of the NEXT_DAY function are shown below:
NEXT_DAY(DATE('1999-06-10'), 1) ==> '1999-06-13'
NEXT_DAY(DATE('1999-06-10'), 4) ==> '1999-06-16'
NEXT_DAY(DATE('1999-06-10'), 5) ==> '1999-06-17'
NEXT_DAY(DATE('1999-06-10'), 6) ==> '1999-06-11'
NEXT_DAY(DATE('1999-06-10'), 7) ==> '1999-06-12'
1999.06.10 was a Thursday.
(37) NUMEDIT
(a) Function
Converts the numeric value specified in argument-1 into character string representation by editing it according to the format specified in argument-2.
(b) Format
[MASTER.]NUMEDIT(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression for argument-1:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression for argument-2:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a numeric data type; argument-2 must be a character string data type (CHAR or VARCHAR). The length of the value of argument-2 must not exceed 250 bytes.
- The data type of the result will be VARCHAR(255).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The value of argument-2 must be specified in the following format:
["character-string"] [+]
{[{<|>}] {9|0|,|"character-string"}...
[,{9|0|,|"character-string"}...]
|{E|e} .integer ["character-string"]}
- "character-string"
- A character string enclosed in double quotation marks represents itself.
- To specify double quotation marks within a character string enclosed in double quotation marks, specify two sets of double quotation marks in succession.
- +
- The [+] symbol specifies the method of sign representation.
- "+" uses the minus sign (-) to represent argument-1 as a negative value; otherwise, it uses a plus sign (+) to represent the value.
- The default for the '+' symbol is a minus sign (-) if the value of argument-1 is negative, and a space character otherwise.
- {< | >}
- The {< | >} symbols specify justification of the character representation of the numeric value.
- The "<" symbol removes all space characters and shifts the character representation to the left.
- The ">" symbol removes all space characters, shifts the character representation to the right, and pads the left side with space characters.
- If neither "<" nor ">" is specified, the character representation is not shifted.
- { 9 | 0 | , | "character-string"} ... [, { 9 | 0 | , | "character-string"}] ...
- The numbers "9" and "0" each represent one digit of the numeric value so that the number of digits associated with the numeric value of argument-1 can be edited.
- The total number of "9"s and "0"s represents the precision of the numeric value after being edited.
- Specifying "0"s produces an edit result equal in digit positions to the number of "0"s specified.
- Specifying a "," produces an edit result that is the comma itself if the edit results on both sides of "," are numeric characters; otherwise, the comma is replaced with one space character.
- The symbol "." represents the decimal point.
- The total number of "9"s and "0"s following the "." indicates the scaling of the numeric value after editing.
- Specifying a "9" before a "." causes the result of editing by "9" to be a space character, provided that the corresponding digit is 0 and the "9" is the first digit, or the edit result to the left of the result of editing by "9" is not a numeric character (except when it is associated with a comma); otherwise, the result of editing by "9" will be a numeric character in the corresponding digit position.
- Specifying a "9" after a "." causes the result of editing by "9" to be an empty (0) character, provided that the corresponding digit is 0 and the "9" is the last digit, or the edit result to the right of the result of editing by "9" is not a numeric character (except when it is associated with a comma); otherwise, the result of editing by "9" will be a numeric character in the corresponding digit position.
- { E | e }.integer is specified in floating-point decimal format.
- The result of the editing is a floating-point decimal format with a scaling for the mantissa specified in integer. The scaling for the mantissa should not exceed 30.
- If the value of argument-1 is a value that cannot be represented by the format specified in argument-2, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
- If low-order digits are truncated by the editing, the result value is rounded off.
(d) Examples
Examples of the NUMEDIT function are shown below:
NUMEDIT(1234567.89, '99,999,990.00"$"') ==>' 1,234,567.89$'
NUMEDIT(1000, '99,999,990.00"$"') ==>' 1,000.00$'
NUMEDIT(1234567.89, '" $"99,999,990.00') ==>' $ 1,234,567.89'
NUMEDIT(1000, '" $"99,999,990.00') ==>' $ 1,000.00'
NUMEDIT(1234567.89, '"$"+99,999,990.00') ==>'$+ 1,234,567.89'
NUMEDIT(-1000, '"$"+99,999,990.00') ==>'$- 1,000.00'
NUMEDIT(1234567.89, '"$">99,999,990.00') ==>' $1,234,567.89'
NUMEDIT(1000, '"$">99,999,990.00') ==>' $1,000.00'
NUMEDIT(1234567.89, '"$"<99,999,990.00') ==>'$1,234,567.89'
NUMEDIT(1000, '"$"<99,999,990.00') ==>'$1,000.00'
NUMEDIT(0.5, '"$"<99,999,990.00') ==>'$0.50'
NUMEDIT(1234567.89, '+E.10"$"') ==>'+1.2345678900E+0.6$'
NUMEDIT(1234567.89, '"$"+e.10') ==>'$+1.2345678900e+0.6'
(38) PI
(a) Function
Returns
, the value of the circle constant.
(b) Format
[MASTER.]PI()
(c) Rules
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). However, because the value of
is always returned, the null value will never be returned.
(d) Example
An example of the PI function is shown below:
PI() ==> 3.14159265358979323846
(39) POSSTR
(a) Function
If in the character string specified in argument-1 the substring specified in argument-2 occurs at least the number of times (nd) specified in argument-4 at or after the character position specified in argument-3, returns the starting position (character position) of the ndth substring.
(b) Format
[MASTER.]POSSTR(argument-1, argument-2, [, argument-3
[, argument-4]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each of the value expressions of argument-1 and argument-2:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in each of the value expressions of argument-3 and argument-4:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR).
- Table 2-56 shows the maximum length for the value of argument-2.
Table 2-56 Maximum length of argument-2 of the POSSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Maximum length of argument-2 |
---|
CHAR or VARCHAR | 255 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
- The data types of argument-3 and argument-4 must be INTEGER or SMALLINT.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- Table 2-57 shows the ranges of values that can be specified in argument-3 and argument-4.
Table 2-57 Ranges of specifiable values in argument-3 and argument-4 of the POSSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Range of values in argument-3 | Range of values in argument-4 |
---|
CHAR(n) or VARCHAR(n) | 1 to n | 1 to n |
MCHAR(n) or MVARCHAR(n) | 1 to n | 1 to n |
NCHAR(n) or NVARCHAR(n) | 1 to n | 1 to n |
- The default for argument-3 is 1; the default for argument-4 is also 1.
- If the length (number of characters) of the character string in argument-2 is 0, the result will be the value of argument-3.
- If the substring matching the character string specified in argument-2 occurs more than the number of times (nd) specified in argument-4 at or after the character position (m) specified in argument-3 in the character string specified in argument-1, the result will be a value greater than or equal to m and equal to the number of characters in the character string specified in argument-1 that are to the left of the beginning of the ndth substring, plus 1. It should be noted that these substrings that match the character string specified in argument-2 are not duplicate substrings. If the substring matching the character string specified in argument-2 does not occur more than the number of times (nd) specified in argument-4 at or after the character position (m) specified in argument-3 in the character string specified in argument-1, the result will be 0.
(d) Examples
Examples of the POSSTR function are shown below:
POSSTR(data base system', 'a') ==> 2
POSSTR(data base system', '') ==> 1 (argument-2 is
POSSTR(data base system', 'a', 5) ==> 7 character string
POSSTR(data base system', 'st') ==> 13 of 0 length.)
POSSTR(data base system', 'a', 1, 3) ==> 7
POSSTR(data base system', 'manager') ==> 0
(40) POWER
(a) Function
Returns the nth power of the value of argument-1, where n denotes the value of argument-2.
(b) Format
[MASTER.]POWER(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be a numeric data type.
- Table 2-58 shows the data type of the result.
Table 2-58 Data type of the result of the POWER system-defined scalar function
Data type of argument-1 | Data type of argument-2 |
---|
SMALLINT or INTEGER | DECIMAL(p,s), SMALLFLT, or FLOAT |
---|
SMALLINT or INTEGER | INTEGER | FLOAT |
DECIMAL(p, s), SMALLFLT, or FLOAT | FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- Specifying a negative value in argument-1 or a non-integer value in argument-2 causes a definition area error (domain error occurs).
- Specifying the value 0 in argument-1 and a non-positive value in argument-2 causes division by zero (division by zero). For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
- If the POWER function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(41) QUARTER
(a) Function
Based on a month specified in argument-2 and a day specified in argument-3 as the beginning of the fiscal year, returns an integer (1, 2, 3, or 4) indicating the fiscal-year quarter in which the date specified in argument-1 falls.
(b) Format
[MASTER.]QUARTER(argument-1 [, argument-2 [, argument-3]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expressions of argument-2 and argument-3:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE.
- The data types and argument-2 and argument-3 must be INTEGER or SMALLINT.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- For argument-2, the default is 1 and the range of specifiable values is 1 to 12.
- For argument-3, the default is 1 and the range of specifiable values is 1 to 29 when the value of argument-2 is 2 and 1 to (number of days in the month specified in argument-2) in all other cases.
- The result is determined according to the following rules:
- If day of argument-1 date < argument-3 day:
Number of months = month of argument-1 date - argument-2 month - 1
- If day of argument-1 date
argument-3:
Number of months = month of argument-1 date - argument-2 month
- If number of months < 0:
Result = (number of months + 12)
4 + 1
- If number of months
0:
Result = number of months
4 + 1
(d) Examples
Examples of the QUARTER function are shown in the following:
QUARTER(DATE('1999-01-01')) ==> 1
QUARTER(DATE('1999-09-10')) ==> 3
QUARTER(DATE('1999-12-31')) ==> 4
QUARTER(DATE('1999-04-01'), 4) ==> 1
QUARTER(DATE('1999-09-10'), 4) ==> 2
QUARTER(DATE('1999-03-31'), 4) ==> 4
QUARTER(DATE('1999-03-21'), 3, 21) ==> 1
QUARTER(DATE('1999-09-20'), 3, 21) ==> 2
QUARTER(DATE('1999-03-20'), 3, 21) ==> 4
(42) RADIANS
(a) Function
Converts to radians the angle specified in degrees in an argument.
(b) Format
[MASTER.]RADIANS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(43) REPLACE (REPLACE_LONG)
(a) Function
Replaces in the character string specified in argument-1 all instances of the character string specified in argument-2 with the character string specified in argument-3.
(b) Format
[MASTER.]REPLACE(argument-1, argument-2[, argument-3])
[MASTER.]REPLACE_LONG(argument-1, argument-2, argument-3)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1, argument-2, and argument-3 must all be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR). In the case of a character string data type or mixed character string type, the length of the argument-2 value or argument-3 value must not exceed 255 bytes; in the case of a national character string data type, the length of the argument-2 value or argument-3 value must not exceed 127 characters.
- If argument-3 is omitted, an empty character string is assumed, and the function deletes all substrings matching the character string specified in argument-2 from the character string specified in argument-1.
- Tables 2-59 and 2-60 show the data type of the result.
Table 2-59 Data type of the result of the REPLACE system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
Table 2-60 Data type of the result of the REPLACE_LONG system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(32000) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(32000) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(16000) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- The length of the result must not exceed the maximum length for the result data type. When the length of the result will exceed the length of the argument-1 character string, the REPLACE_LONG function should be used.
(d) Examples
Examples of the REPLACE function are shown below:
REPLACE('a big dog and a small dog', 'dog', 'cat')
==> 'a big cat and small cat'
REPLACE('a big dog and a small dog', 'big ')
==> 'a dog and a small dog'
(44) REVERSESTR
(a) Function
Returns a character string that is the reverse of the character string specified in an argument (the returned character string reads from left to right the same as the specified character string reads from right to left).
(b) Format
[MASTER.]REVERSESTR(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR).
- Table 2-61 shows the data type of the result.
Table 2-61 Data type of the result of the REVERSESTR system-defined scalar function
Data type of argument | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the REVERSESTR function are shown below:
REVERSESTR('data base') ==> 'esab atad'
REVERSESTR('esab atad') ==> 'data base'
REVERSESTR(' esab atad') ==> 'data base '
(45) RIGHTSTR
(a) Function
Returns from the end (the rightmost position) of the character string specified in argument-1 the substring consisting of the number of characters specified in argument-2.
(b) Format
[MASTER.]RIGHTSTR(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression of argument-2:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string type data type (NCHAR or NVARCHAR).
- The data type of argument-2 must be INTEGER or SMALLINT.
- Table 2-62 shows the range of values that can be specified in argument-2.
Table 2-62 Range of specifiable values in argument-2 of the RIGHTSTR system-defined scalar function
Data type of argument-1 | Range of values in argument-2 |
---|
CHAR(n) or VARCHAR(n) | 0 to n |
MCHAR(n) or MVARCHAR(n) | 0 to n |
NCHAR(n) or NVARCHAR(n) | 0 to n |
- Table 2-63 shows the data type of the result.
Table 2-63 Data type of the result of the RIGHTSTR system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- If the number of characters in the argument-1 character string is less than the number of characters specified in argument-2, the result will be the argument-1 value.
(d) Examples
Examples of the RIGHTSTR function are shown below:
RIGHTSTR('data base system', 6) ==> 'system'
RIGHTSTR('data system', 0) ==> ''
(Character string of 0 length)
(46) ROUND
(a) Function
Rounds the value of argument-1 following the nth place after the decimal point (number of digits following the decimal point given by 10 -n), where n is the value of argument-2.
Specifying argument-3 causes the function to round up if the value of argument-1 at the rightmost digit position given by 1
10 (-n-1) is greater than or equal to the value of argument-3; otherwise, the function rounds down.
(b) Format
[MASTER.]ROUND(argument-1, [, argument-2[, argument-3]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a numeric data type; the data types of argument-2 and argument-3 must be INTEGER or SMALLINT.
- The default for argument-2 is 0. Table 2-64 shows the range of values that can be specified in argument-2.
Table 2-64 Range of specifiable values in argument-2 of the ROUND system-defined scalar function
Data type of result | Range of values in argument-2 |
---|
INTEGER | -9 to 0 |
DECIMAL(p, s) | -(p - s- 1) to s |
FLOAT | HP-UX (32-bit mode): -307 to 307 HP-UX (64-bit mode), Solaris, AIX 5L, Linux, and Windows: -307 to 323 |
- Table 2-65 shows the data type of the result.
Table 2-65 Data type of the result of the ROUND system-defined scalar function
Data type of argument-1 | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- The default for argument-3 is 5. The range of values that can be specified in argument-3 is 1 to 9. If the value of argument-1 at the rightmost digit position given by 1
10 (-n-1) is greater than or equal to the value of argument-3, the value of argument-1 is rounded up; otherwise, it is rounded down. - If the ROUND function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(47) ROUNDMONTH
(a) Function
Rounds off the date specified in argument-1 to the first day of either the specified date's month or the following month, using the day of the month specified in argument-2 to determine the point for rounding up to the following month. This function can be used, for example, to change a date on or after the 20th of the month to the first of the next month.
(b) Format
[MASTER.]ROUNDMONTH(argument-1 [, argument-2])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression for argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression for argument-2:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE; the data type of argument-2 must be INTEGER or SMALLINT.
- The data type of the result will be DATE.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The default for argument-2 is 16. The range of values that can be specified in argument-2 is 1 to 32.
- The result is determined according to the following rules:
- If day of argument-1 < argument-2:
Year of result = year of argument-1
Month of result = month of argument-1
Day of result = 1
- If day of argument-1
argument-2:
If month of argument-1 < 12:
Year of result = year of argument-1
Month of result = month of argument-1 + 1
Day of result = 1
If month of argument-1 = 12:
Year of result = year of argument-1 + 1
Month of result = 1
Day of result = 1
- If the result of the ROUNDMONTH function is not within the range 0001/01/01 - 9999/12/31, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(d) Examples
Examples of the ROUNDMONTH function are shown below:
ROUNDMONTH(DATE('1999-08-15')) ==> '1999-08-01'
ROUNDMONTH(DATE('1999-08-16')) ==> '1999-09-01'
ROUNDMONTH(DATE('1999-09-20'), 21) ==> '1999-09-01'
ROUNDMONTH(DATE('1999-09-21'), 21) ==> '1999-10-01'
ROUNDMONTH(DATE('1999-12-21'), 21) ==> '2000-01-01'
ROUNDMONTH(DATE('1999-03-31'), 32) ==> '1999-03-01'
ROUNDMONTH(DATE('1999-02-29'), 29) ==> '2000-03-01'
ROUNDMONTH(DATE('1999-03-31'), 29) ==> '2000-04-01'
ROUNDMONTH(DATE('9999-12-31'), 26) ==> Overflow
(48) RTRIM
(a) Function
Beginning at the right end of the character string specified in argument-1, removes all instances of each of the characters in the character string specified in argument-2 until a character not found in the argument-2 character string is encountered.
(b) Format
[MASTER.]RTRIM(argument-1 [, argument-2])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR).
- The maximum length of the argument-2 value is 30 bytes for a character string or mixed character string data type and 30 characters for a national character string data type.
- Table 2-66 shows the data type of the result.
Table 2-66 Data type of the result of the RTRIM system-defined scalar function
Data type of argument-1 (and argument-2) | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- If argument-2 is omitted, one space character in the data type of the result is assumed.
(d) Examples
Examples of the RTRIM function are shown below:
RTRIM('data basebcabcabc', 'abc') ==> 'data basebc'
RTRIM('database ') ==> 'data base'
(49) RTRIMSTR
(a) Function
Beginning at the right end of the character string specified in argument-1, deletes each successive occurrence of the character string specified in argument-2 until it does not find that character string.
(b) Format
[MASTER.]RTRIMSTR(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR).
- Table 2-67 shows the maximum length for the value of argument-2.
Table 2-67 Maximum length of argument-2 of the RTRIMSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Maximum length of argument-2 |
---|
CHAR or VARCHAR | 255 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
- Table 2-68 shows the data type of the result.
Table 2-68 Data type of the result of the RTRIMSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
(d) Example
An example of the RTRIMSTR function is shown below:
RTRIMSTR('data basebcabcabc', 'abc') ==> 'data basebc'
(50) SIGN
(a) Function
Returns the sign of an argument (+1 for positive, -1 for negative, 0 for zero).
(b) Format
[MASTER.]SIGN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- Table 2-69 shows the data type of the result.
Table 2-69 Data type of the result of the SIGN system-defined scalar function
Data type of argument-1 | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(1,0) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(51) SIN
(a) Function
Returns the sine (SIN trigonometric function) of an argument in which an angle is specified in radians.
(b) Format
[MASTER.]SIN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(52) SINH
(a) Function
Returns the hyperbolic sine of an argument.
(b) Format
[MASTER.]SINH(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the SINH function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(53) SQRT
(a) Function
Returns the square root of the value of an argument.
(b) Format
[MASTER.]SQRT(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- Specifying a negative value in the argument causes a definition area error (domain error occurs).
(54) STRTONUM
(a) Function
Converts the character string representation of the numeric value specified in argument-1 into a numeric data type. Conversion is into INTEGER when the data type of argument-2 is INTEGER or SMALLINT; into DECIMAL with the same precision and scaling when the data type of argument-2 is DECIMAL; and into FLOAT when the data type of argument-2 is FLOAT or SMALLFLT.
(b) Format
[MASTER.]STRTONUM(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression for argument-1:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression for argument-2:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called..
- argument-1 must be a character string data type (CHAR or VARCHAR). The length of the character string specified in argument-1 must not exceed 255 bytes.
- argument-2 must be a numeric data type.
- Table 2-70 shows the data type of the result.
Table 2-70 Data type of the result of the STRTONUM system-defined scalar function
Data type of argument-2 | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 is the null value, the result is also the null value. The value of argument-2 does not affect the result.
- Specify the value of the character string in argument-1 in the following format:
- Data type of argument-2 is INTEGER:
[space-character...] [+|-] [space-character ...] numeric-character...
[space-character ...]
- Data type of argument-2 is DECIMAL:
[space-character...] [+|-] [space-character...]
|numeric-character...[. [numeric-character...]]
| .numeric-character...| [space-character...]
- Data type of argument-2 is FLOAT:
[space-character...] [+|-] [space-character...]
|numeric-character... [. [numeric-character...]]
| .numeric-character...|
[{E|e} [+|-] numeric-character...] [space-character...]
- If the STRTONUM function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
- When the data type of argument-2 is DECIMAL and low-order digits are truncated by the data type conversion, the result value is rounded off.
(d) Examples
Examples of the STRTONUM function are shown below:
- When argument-2 is INTEGER:
STRTONAM(' - 1234567 ', 0)
==> -1234567 (INTEGER type)
STRTONUM(' + 1234567890123 ', 0)
==> Overflow
STRTONUM(' 1234567.89 ', 9)
==> Invalid value in argument-1 in STRTONUM function
during conversion to INTEGER
- When argument-2 is DECIMAL:
STRTONUM(' -1234567 ', 123456789012.
==> -1234567 (DECIMAL(12, 0) type)
STRTONUM(' 1234567.89 ', 9999999999.999)
==. 1234567.89 (DECIMAL(13, 3) type)
STRTONUM(' 1234567.89 ', 99999.999)
==> Overflow
STRTONUM(' 1.23456789E6 ', 9999999999.999)
==> Invalid value in argument-1 in STRTONUM function
during conversion to DECIMAL
- When argument-2 is FLOAT:
STRTONUM(' 1234567.89 ', 1e0)
==> 1.23456789e6 (FLOAT type)
STRTONUM(' -1234567890123 ', 0e0)
==> -1.234567890123E12 (FLOAT type)
STRTONUM(' 1.23456789E6 ', 1E1)
==> 1.23456789E6 (FLOAT type)
STRTONUM(' 1.0E310 ', 9E9)
==> Overflow
(55) TAN
(a) Function
Returns the tangent (TAN trigonometric function) of an argument in which an angle is specified in radians.
(b) Format
[MASTER.]TAN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the TAN function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(56) TANH
(a) Function
Returns the hyperbolic tangent of an argument.
(b) Format
[MASTER.]TANH(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the TANH function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(57) TRANSL (TRANSL_LONG)
(a) Function
If any of the characters in the character string specified in argument-2 is included in the character string specified in argument-1, returns a character string in which those characters are translated into the corresponding characters in the character string specified in argument-3, where the ith character in the character string specified in argument-2 corresponds to the ith character in argument-3. If the number of characters in argument-3 is less than the number of characters in the character string of argument-2, for the character string in argument-3, HiRDB assumes a character string that is obtained by repeatedly filling the character string in argument-3 with the characters in argument-4.
(b) Format
[MASTER.]TRANSL(argument-1, argument-2, argument-3[, argument-4])
[MASTER.]TRANSL_LONG(argument-1, argument-2, argument-3
[, argument-4])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal (for TRANS_LONG, mixed character string literal only)
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- If there are fewer characters in the character string specified in argument-3 than in the character string specified in argument-2, the character specified in argument-4 is used to pad out the argument-3 character string to the same length as the argument-2 character string.
- Only one character can be specified in argument-4. The default for argument-4 is the space character (the double-byte space character when the data type of argument-1 is NCHAR or NVARCHAR).
- For the TRANSL function, the data type of all arguments must be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR). For the TRANSL_LONG function, the data type of all arguments must be MCHAR or MVARCHAR.
- Table 2-71 shows the maximum lengths for the values of argument-2 and argument-3.
Table 2-71 Maximum lengths of argument-2 and argument-3 of the TRANSL and TRANSL_LONG system-defined scalar functions
Data type of argument-1 | Maximum length of argument-2 and argument-3 |
---|
CHAR or VARCHAR* | 255 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR* | 127 |
* Not specifiable for TRANSL_LONG.
- Tables 2-72 and 2-73 show the data type of the result.
Table 2-72 Data type of the result of the TRANSL system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
Table 2-73 Data type of the result of the TRASNSL_LONG system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | Not specifiable |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(32000) |
NCHAR(n) or NVARCHAR(n) | Not specifiable |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
(d) Examples
Examples of the TRANSL and TRANSL_LONG functions are shown below:
TRANSL('data base system', 'abcdefghijklmnopqrstuvwxyz',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
==> 'DATA BASE SYSTEM' (Translation of lowercase characters
into uppercase characters)
TRANSL('<data base> system', '[ ] { } ( ) < >', '')
==> ' data base system '
(argument-3 is a character string with a length of 0, and all
enclosure symbols (such as [, ], {, }, (, ), <, >) are translated into spaces)
TRANSL('+12345.678', '+-012345678', 'SS', '9')
==> 'S99999.999'
TRANSL (M' d a t a b a s e s y s t e m'
M' 0 1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n
o p q r s t u v w x y z'
M'0123456789abcdefghijklmnopqrstuvwxyz')
==> M'data base system' (Translation of double-byte characters
into single-byte characters)
TRANSL_LONG(M'data base system',
M'0123456789abcdefghijklmnopqrstuvwxyz',
M'0 1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n
o p q r s t u v w x y z')
==> M'd a t a b a s e s y s t e m'
(Translation of single-byte characters into double-byte characters)
TRANSL('+12345.678', '+012345678', '', 'S')
==> 'SSSSSS.SSS'
TRANSL('2000-03-31 12:23:30', '-:', '/.')
==> '2000/03/31 12.23.30'
(58) TRUNC
(a) Function
Returns the value obtained by rounding off the value of argument-1 at the nth place after the decimal point (number of digits following the decimal point given by 10-n), where n is the value specified in argument-2.
(b) Format
[MASTER.]TRUNC(argument-1 [, argument-2])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a numeric data type; the data type of argument-2 must be INTEGER or SMALLINT.
- The default for argument-2 is 0. Table 2-74 shows the range of values that can be specified in argument-2.
Table 2-74 Range of specifiable values in argument-2 of the TRUNC system-defined scalar function
Data type of result | Range of values in argument-2 |
---|
INTEGER | 0 to 9 |
DECIMAL(p, s) | (p - s - 1) to s |
FLOAT | HP-UX (32-bit mode): -307 to 307 HP-UX (64-bit mode) Solaris, AIX 5L, Linux, and Windows: -307 to 323 |
- Table 2-75 shows the data type of the result.
Table 2-75 Data type of the result of the TRUNC system-defined scalar function
Data type of argument-1 | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
(59) TRUNCYEAR
(a) Function
Based on a month specified in argument-2 and a day specified in argument-3 as the beginning of the fiscal year, returns the first day of the fiscal year in which the date specified in argument-1 falls. This function can be used to determine a fiscal year that ends, for example, on March 20 or on the last day of March.
(b) Format
[MASTER.]TRUNCYEAR(argument-1[, argument-2[, argument-3]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expressions of argument-2 and argument-3:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE.
- The data types of argument-2 and argument-3 must be INTEGER or SMALLINT.
- The data type of the result will be DATE.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- For argument-2, the default is 1 and the range of values that can be specified is 1 to 12.
- For argument-3, the default is 1 and the range of values that can be specified is 1 to 29 when the value of argument-2 is 2 and 1 to (number of days in the month specified in argument-2) in all other cases.
- The result is determined according to the following rules:
- If month of argument-1 < argument-2 or month of argument-1 = argument-2 and day of argument-1 < argument-3:
Year of result = year of argument-1 - 1
- If month of argument-1 > argument-2 or month of argument-1 = argument-2 and day of argument-1
argument-3:
Year of result = year of argument-1
- If year of result is non-leap year and argument-2 = 2 and argument-3 = 29:
Month of result = 3
Day of result = 1
- If the year of the result is a leap year and argument-2 is not 2, or argument-3 is not 29:
Month of result = argument-2
Day of result = argument-3
- If the result of the TRUNCYEAR function is not within the range 0001/01/01 - 9999/12/31, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(d) Examples:
Examples of the TRUNCYEAR function are shown below:
TRUNCYEAR(DATE('1999-09-10')) ==> '1999-01-01'
TRUNCYEAR(DATE('1999-09-11'), 4) ==> '1999-04-01'
TRUNCYEAR(DATE('1999-03-31'), 4) ==> '1999-04-01'
TRUNCYEAR(DATE('1999-08-11'), 3, 21) ==> '1999-03-21'
TRUNCYEAR(DATE('1999-03-20'), 3, 21) ==> '1999-03-21'
TRUNCYEAR(DATE('2000-02-28'), 2, 29) ==> '1999-03-01'
(1999-02-29 is a nonexistent date.)
TRUNCYEAR(DATE('2000-03-20'), 2, 29) ==> '2000-02-29'
TRUNCYEAR(DATE('0001-03-20'), 4) ==> Overflow
(60) WEEK
(a) Function
Returns an integer, in the range 1 to 54, that represents the number of the week since the beginning of the year in which the date specified in an argument falls (assuming that each week begins on Sunday).
(b) Format
[MASTER.]WEEK(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the WEEK function are shown below:
WEEK(DATE('2000-01-01')) ==> 1
WEEK(DATE('2000-01-02')) ==> 2
WEEK(DATE('2000-02-29')) ==> 10
WEEK(DATE('2000-12-30')) ==> 53
WEEK(DATE('2000-12-31')) ==> 54
2000-01-01 is a Saturday, and 2000-12-31 is a Sunday.
(61) WEEKOFMONTH
(a) Function
Returns an integer, in the range 1 to 6, that represents the number of the week since the beginning of the month in which the date specified in an argument falls.
(b) Format
[MASTER.]WEEKOFMONTH(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the WEEKOFMONTH function are shown below:
WEEKOFMONTH(DATE('2000-01-01')) ==> 1
WEEKOFMONTH(DATE('2000-01-02')) ==> 2
WEEKOFMONTH(DATE('2000-01-29')) ==> 5
WEEKOFMONTH(DATE('2000-01-30')) ==> 6
2000-01-01 is a Saturday, and 2000-01-31 is a Sunday.
(62) YEARS_BETWEEN
(a) Function
Returns as a real number (FLOAT type) the number of years between two dates specified in arguments.
(b) Format
[MASTER.]YEARS_BETWEEN(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- CURRENT DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data types of argument-2 and argument-3 must be DATE.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The number of years which is the result of YEARS_BETWEEN is calculated according to the following rules:
- If date-1 < date-2:
Result = -YEARS_BETWEEN (date-2, date-1)
- If date-1
date-2:
Let y be an integer greater than or equal to 0, and let date-4 and date-3 be the date (date-2 + y years) and (date-2 + (y + 1)) years, respectively, such that date-4
date-1 < date-3
Let d1 and d2 be the number of days between date-4 and date-1 (DAYS (date-1) - DAYS (date-4)) and the number of days between date-4 and date-3 (DAYS (date-3) - DAYS (date-4)), respectively (where d2 is one of the following values: 365, 366)
The number of years in the result will be (y + d1
d2) years.
Figure 2-7 shows the relationships between the dates date-1, date-2, date-3, and date-4:
Figure 2-7 Relationships between the dates date-1, date-2, date-3, and date-4
![[Figure]](figure/zu2s0460.gif)
(d) Examples
Examples of the YEARS_BETWEEN function are shown below:
YEARS_BETWEEN(DATE('2000-06-10'),DATE('1999-06-10'))==> 1
YEARS_BETWEEN(DATE('2000-06-11'),DATE('1999-06-10'))==> 1.002739...
YEARS_BETWEEN(DATE('1999-06-11'),DATE('1999-06-10'))==> 1.002732...
YEARS_BETWEEN(DATE('2014-09-09'),DATE('1999-06-10'))==> 15.249315...
YEARS_BETWEEN(DATE('1999-06-10'),DATE('1999-06-10'))==> -15.249315...