2.16.1 System built-in scalar functions
(1) ABS
(a) Function
The ABS scalar function returns the absolute value of a value expression.
(b) Format
ABS(value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Numeric literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of a value expression must be numeric data, date interval data, or time interval data.
- The data type of the result will be the same as the data type of the value expression.
- The NOT NULL constraint does not apply to the result value (the null value is allowed). If the value expression is the null value, the result will also be the null value.
- The result must be a value that can be expressed as the absolute value of the value expression. If a value that cannot be expressed as the absolute value is specified, an overflow error occurs (for the result when overflow error suppression is set, see 2.18 Operational results with overflow error suppression specified).
(2) BIT_AND_TEST
(a) Function
Determines a bit-by-bit logical product of value-expression-1 and value-expression-2, and returns the BOOLEAN value TRUE if any of the bits in the results of the logical product is 1.
(b) Format
BIT_AND_TEST(value-expression-1, value-expression-2) |
(c) Rules
- The following items can be specified in value-expression-1 and value-expression-2:
- Literals
- USER
- Column specification
- Component specification
- SQL variables or SQL parameters
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Embedded variables or ? parameters
- Function calls
- Scalar subquery
- The data type of value-expression-1 and value-expression-2 must be either a character data type (CHAR or VARCHAR) or BINARY with a maximum length of 32,000 bytes. The following table indicates the combinations of data types that can be specified in value-expression-1 and value-expression-2.
Table 2-29 Combinations of data types that can be specified in value-expression-1 and value-expression-2 (system built-in scalar function BIT_AND_TEST)
Data type of value expression 1 | Data type of value expression 2 |
---|
Character data (CHAR and VARCHAR) | Binary data (BINARY) |
---|
Character data (CHAR and VARCHAR) | Y | N# |
Binary data (BINARY) | N# | Y |
- Legend:
- Y: Can be specified.
- N: Cannot be specified.
- #: Only hexadecimal character string literals can be specified as a character data value expression.
- Value expressions consisting solely of embedded variables or ? parameters cannot be specified in both value-expression-1and value-expression-2.
- If one value expression is an embedded variable or a ? parameter, HiRDB assumes that the data type of the embedded variable or ? parameter is VARCHAR, provided that the data type of the other value expression is character data, and BINARY if the data type of the other value expression is binary data. Similarly, HiRDB assumes that the data length of the embedded variable or ? parameter is equal to the data length of the other value expression.
- If value-expression-1 and value-expression-2 are both character string data types, use the same character set for value-expression-1 and value-expression-2. However, if either value-expression-1 or value-expression-2 is one of the value expressions listed below, it is converted to the character set of the corresponding value expression:
- Character string literal
- Embedded variable (default character set)
- ? parameter
- If value-expression-1and value-expression-2 have different data lengths, HiRDB fills the shorter data with X'00' on the right and determines a bit-by-bit logical product after making the two value expressions equal in character string length.
- The data type of the result is the BOOLEAN type.
- The value of the result is not NOT NULL constrained (the null value is allowed). If either value-expression-1or value-expression-2 is the null value, the result also is the null value.
- Determines a bit-by-bit logical product of value-expression-1 and value-expression-2, and the result is TRUE if any of the bits in the results of the logical product is 1; it is FALSE otherwise.
- If both value-expression-1and value-expression-2 are character strings of a length 0, the result is FALSE.
(d) Notes
The BIT_AND_TEST scalar function can be specified in the following locations:
- Value expressions in a logical predicate in a search condition
- RETURN statement in CREATE FUNCTION for which the data type of the return value is the BOOLEAN type
(e) Example
Performs a test to determine if C1 in a column of table T1 (data type: VARCHAR(2)) contains bits.
SELECT * FROM T1
WHERE BIT_AND_TEST(C1,X'FFFF') IS TRUE
(3) CHARACTER
(a) Function
Converts date data, time data, or time stamp data into a character string representation.
(b) Format
CHAR[ACTER](value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Columns of the date data type, time data type, or time stamp data type
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations that produce date data type results
- Time operations that produce time data type results
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of value-expression must be the date data type (DATE), time data type (TIME), or time stamp data type (TIMESTAMP).
- The data types of the result are as follows:
- If value-expression is the date data type:
- CHAR(10)
- If value-expression is the time data type:
- CHAR(8)
- If value-expression is the time stamp data type:
- CHAR(19), CHAR(22), CHAR(24), or CHAR(26)
- The value of the result is a predefined character string representation of the data type of value-expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the source value expression is the null value, the result is also the null value.
- The character set of the result is the default character set.
(d) Examples
- Update any dates older than 6 months in column C1 of table T1 (CHAR data type) to the date 02-06-1995:
UPDATE T1
SET C1=CHAR(CURRENT_DATE)
WHERE CHAR(CURRENT_DATE - 6 MONTHS) > C1
![[Figure]](figure/zu2s0180.gif)
- Update the time 0:0:0 in column C1 of table T2 (CHAR data type) to the time 14:24:45:
UPDATE T2
SET C1=CHAR(CURRENT_TIME)
WHERE C1='00:00:00'
![[Figure]](figure/zu2s0190.gif)
(4) DATE
(a) Function
- The DATE scalar function performs the following conversions:
- Converts the character string representation of a date in a specified format into date data.
- Converts the cumulative number of days since January 1, 1 (Gregorian calendar) into equivalent date data.
(b) Format
- Format of function 1
DATE(value-expression[,datetime-format])
- Format of function 2
DATE(value-expression)
(c) Rules for function 1
- The following items can be specified as the value expression:
- Literals that are character string expressions of dates
- CURRENT_DATE
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations that produce date data type results
- Concatenation operation
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The value expression must be one of the following data types:
- A datetime format is specified:
Character string data type with a defined length of 8 to 255 bytes (CHAR, VARCHAR). However, if the character set is UTF-16, it is a character string data type with a defined length of 16 to 510 bytes.
Mixed character string data type (MCHAR, MVARCHAR)
- A datetime format is not specified:
Character string data type with a defined length of 10 bytes (CHAR, VARCHAR). However, if the character set is UTF-16, it is a character string data type with a defined length of 20 bytes (CHAR, VARCHAR).
Date data type (DATE)
- If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- The value expression must be the character string representation of a date in a format specified in a datetime format. If a datetime format is omitted, the value expression must be the predefined character string representation of a date.
Examples:
Datetime format 'YYYY/MM/DD'
'1995/06/30'
Datetime format omitted
'1995-06-30'
- If the value expression is of the date data type, the result will be the equivalent date.
- For datetime formats, see 1.11 Specifying a datetime format.
- When specifying a datetime format, use the same character set for the value expression and datetime format. However, if the datetime format is the value expression listed below, it is converted to the character set of the following value expression:
(d) Rules for function 2
- The following items can be specified as the value expression:
- Numeric literals
- Column specifications
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be an integer (INTEGER).
If an arithmetic operation, set function, or CASE expression is specified, the result of the operation must be the integer data type.
- The allowable range of values is 1 to 3652059.
- The result is the date (specified numeric value - 1) from January 1, 1 (Gregorian calendar).
Example: If the value expression is 35, then the date is February 4, 1 (Gregorian calendar).
(e) Common rules
- The data type of the result is the date data type (DATE).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression or the datetime format is the null value, the result also is the null value.
(f) Example
- The following uses the DATE scalar function to perform the same processing as the example in the section on the CHARACTER scalar function:
UPDATE T1
SET C1=CHAR(CURRENT DATE)
WHERE CURRENT DATE - 6 MONTHS > DATE(C1)
- Obtains date data from the character string, in a format other than a predefined character string representation ('DD/MON/YYYY') of a date in column C1 (data type: CHAR) in table T2:
SELECT DATE(C1,'DD/MON/YYYY') FROM T2
![[Figure]](figure/zu2s0510.gif)
(5) DAY
(a) Function
Extracts the day part from date data, time stamp data, or date interval data.
(b) Format
DAY (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_DATE
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be either the date data type (DATE), time stamp data type (TIMESTAMP), or the date interval data type (INTERVAL YEAR TO DAY).
- The data type of the result is integer (INTEGER).
- If the value expression is of the date data type or time stamp data type, the results will be in the range 1 to 31.
- If the value expression is of the date interval data type, the result will be in the range -99 to 99.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the source value expression is the null value, the result is also the null value.
(d) Example
Retrieve all rows with the current date from the rows in column C1 (date data type data type) of table T1:
SELECT * FROM T1
WHERE DAY(C1)=DAY(CURRENT_DATE)
(6) DAYS
(a) Function
Converts date data or time stamp data into a cumulative number of days since January 1, year 1 (A.D.).
(b) Format
DAYS (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Predefined character string representation literals of a date
- CURRENT_DATE
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations that produce date data type results
- Concatenation operations producing a result that is the predefined character string representation literal of a date
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be the date data type (DATE) or time stamp data type (TIMESTAMP).
- The data type of the result must be an integer (INTEGER).
- The result of executing the DAYS scalar function on a specified date is the cumulative number of days, including the specified date, since January 1, 1 (Gregorian calendar).
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Determine the number of days through the current date (06-30-1995) since the value in column C1 of table T1:
SELECT DAYS(CURRENT_DATE) - DAYS(C1)
FROM T1
(7) DECIMAL
(a) Function
The DECIMAL scalar function converts numeric data into decimal data.
(b) Format
DEC[IMAL] (value-expression [, precision [, decimal-scaling-position]])
(c) Rules
- The following items can be specified as the value expression:
- Numeric literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The following items can be specified as the data type of value-expression:
- The precision must be an integer in the range 1 to 38.
If the precision is not specified, the assumed value varies depending on the data type of the specified value expression. The following table indicates the precision assumed when it is not specified.
Table 2-30 Default precisions of the DECIMAL scalar function
Data Type | Precision (number of digit positions) |
---|
INTEGER | 10 |
SMALLINT | 5 |
DECIMAL | 15 |
FLOAT | When the pd_sql_dec_op_maxprec operand of the system common definition is 29 or omitted#: 29 When the pd_sql_dec_op_maxprec operand of the system common definition is 38#: 38 |
SMALLFLT |
- #
- For details about the pd_sql_dec_op_maxprec operand of the system common definition, see the manual HiRDB Version 9 System Definition.
- Scaling is specified in the range of values from 0 to the specified precision. The scaling must either be an integer or a character string representation of an integer. The default scaling is 0.
- The following table lists the data type of the result.
Table 2-31 Data type of the result of the DECIMAL scalar function
Data Type | Precision of result | Decimal scaling position of the result |
---|
DECIMAL | Precision specified in the argument. If not specified, then the precision shown in Table 2-30. | Decimal scaling position specified in the argument. If not specified, then 0. |
- The integer part of the value expression must be expressed by a value that is within the specified precision and decimal scaling position. If the integer part exceeds the specified precision, an overflow error results.
- Any digits following the specified decimal scaling position in the result are rounded off.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Ifthe pd_sql_dec_op_maxprec operand of the system common definition is 29, or if this operand was omitted, the result obtained by dividing column C1 (data type: DECIMAL(10,0)) by column C2 (data type: INTEGER) in table T1 is decimal data DEC(29,2), and the scalar function DECIMAL can be used to delete unneeded digits and convert the data to DEC(4,2).
SELECT DECIMAL(C1/C2,4,2)
FROM T1
![[Figure]](figure/zu2s0200.gif)
(8) DIGITS
(a) Function
The DIGITS scalar function extracts the digits part of an integer, decimal number, date interval data, or time interval data and converts it into a character string expression.
(b) Format
DIGITS (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Integer or decimal literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Date operations that produce results that are of the date interval data type
- Time operations that produce results that are of the time interval data type
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be one of the following:
- Integer (INTEGER, SMALLINT)
- Fixed-point number (DECIMAL)
- Date interval data type (INTERVAL YEAR TO DAY)
- Time interval data type (INTERVAL HOUR TO SECOND)
- The data type of the result is a fixed-length character string (CHAR).
- The length of the resulting data varies depending on the data type of the value expression. The following table lists the data lengths of the result.
Table 2-32 Data lengths of the result of the DIGITS scalar function
Data type | Data length of result |
---|
INTEGER | 10 |
SMALLINT | 5 |
DECIMAL(p,s) | p |
INTERVAL YEAR TO DAY | 8 |
INTERVAL HOUR TO SECOND | 6 |
p: Precision.
s: Decimal scaling position.
- The result is a character string expression of the absolute value of the value expression, without a sign or decimal point. If the actual value has fewer digits than the data length of the destination field, the destination field is filled with leading zeros.
Example: The data type of the values to be converted is DECIMAL(4,1):
15.
'0150'
-12.4
'0124'
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
- The character set of the result is the default character set.
(d) Example
Retrieve data for which the value of column C1 (CHAR data type) and the value of column C2 (DECIMAL data type) in table T3 are equal:
SELECT * FROM T3
WHERE C1 = DIGITS(C2)
![[Figure]](figure/zu2s0210.gif)
(9) FLOAT
(a) Function
The FLOAT scalar function converts numeric data into floating-point data.
(b) Format
FLOAT (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Numerical literals
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The following items can be specified as the data type of value-expression:
- The data type of the result must be a double-precision floating-point number (FLOAT).
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Obtain the result in floating-point data of dividing column C1 (INTEGER data type) by column C2 (INTEGER data type) in Table T1; use the FLOAT scalar function before the division operation to convert either operand to the FLOAT type:
SELECT FLOAT(C1)/C2 FROM T1
![[Figure]](figure/zu2s0220.gif)
(10) HEX
(a) Function
The HEX scalar function converts a value expression into a hexadecimal character string expression.
(b) Format
HEX (value-expression)
(c) Rules
- The HEX scalar function converts the format of the value expression, represented internally in the system, into a hexadecimal character string expression.
The following table lists the formats of internal representations and provides examples of execution results.
Table 2-33 Formats of internal representations by the HEX scalar function and examples of execution results
Value expression | Internal representation format | HEX(value-expression) |
---|
'#AB12' | Data type: CHAR(5) 23|41|42|31|32 | '2341423132' |
1234 | Data type: INTEGER In Windows: D2|04|00|00 In UNIX: 00|00|04|D2 | In Windows: 'D2040000' In UNIX: '000004D2' |
1234. | Data type: DECIMAL(4,0) 01| 23| 4C | '01234C' |
- The following items can be specified as the value expression:
- Literals
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Date operations
- Time operations
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The following table indicates the relationship between the data types that can be specified in a value expression and the resulting data type and data length.
Table 2-34 Relationship between the data type of a value expression for the HEX scalar function and the data type and data length of a result
Value expression | Execution result |
---|
Data type | Defined length | Actual length | Data type | Defined length | Actual length |
---|
CHAR(n) | 1 n < 128 | -- | CHAR | n*2 | -- |
128 n 16,000 | VARCHAR | n*2 |
NCHAR(n) | 1 n < 64 | CHAR | n*4 | -- |
64 n 8,000 | VARCHAR | n*4 |
MCHAR(n) | 1 n < 128 | CHAR | n*2 | -- |
128 n 16,000 | VARCHAR | n*2 |
VARCHAR(n) | 1 n 16,000 | [r] | VARCHAR | n*2 | r*2 |
NVARCHAR(n) | 1 n 8,000 | n*4 | r*4 |
MVARCHAR(n) | 1 n 16,000 | n*2 | r*2 |
INTEGER | -- | -- | CHAR | 8 | -- |
SMALLINT | 4 |
DECIMAL(P,S) | 1 P 38 0 S 38 S P | ( P/2 + 1) * 2 |
FLOAT | -- | 16 |
SMALLFLT | 8 |
DATE | 8 |
TIME | 6 |
TIMESTAMP(p) | p = 0, 2, 4, or 6 | (7+p/2)*2 |
INTERVAL YEAR TO DAY | -- | 10 |
INTERVAL HOUR TO SECOND | 8 |
BINARY(n) | 1 n 16,000 | [r] | VARCHAR | n*2 | r*2 |
P: Precision
S: Decimal scaling position
p: Fractional second precision
--: Not applicable
Note: If only a character string literal (including national and mixed character string literals) whose length is 0 is specified in the value expression, the defined length of the execution result will be 1.
- If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- The HEX scalar function cannot be specified for a value expression if execution of the value expression produces any of the following data types:
- CHAR, VARCHAR, MCHAR, or MVARCHAR with a minimum length of 16,001 bytes
- NCHAR or NVARCHAR with a minimum length of 8,001 characters
- BLOB
- BINARY with a minimum length of 16,001 bytes
- BOOLEAN
- A value expression that contains embedded variables or ? parameters cannot be specified.
- If an operand in the value expression or the argument of the function is composed solely of literals, the HEX scalar function cannot be specified if it produces a result with a length exceeding 255 bytes.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
- In the Windows edition, the result value (numeric data excluding the DECIMAL type) is represented internally in Little Endian. Specifically, the value 1234 of the INTEGER type is represented internally as D2 04 00 00 and the execution result is represented as D2040000.
- In the UNIX edition, the result value depends on the internal expression of the server platform. For example, in the case of Linux running on an Intel family CPU, numeric data excluding the DECIMAL type is represented internally in Little Endian. Specifically, the value 1234 of the INTEGER type is represented internally as D2 04 00 00, and the execution result is represented as D2040000.
- If the data type of the result is character string data, the character set of the result is the default character set.
(11) HOUR
(a) Function
Extracts the time part from time data, time stamp data, or time interval data.
(b) Format
HOUR (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Time operations
- Set functions (MAX, MIN)
- Scalar functions (ABS, TIME, VALUE)
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be the time data type (TIME), time stamp data type (TIMESTAMP), or time interval data type (INTERVAL HOUR TO SECOND).
- The data type of the result is integer (INTEGER).
- If the value expression is of the time data type or the time stamp data type, the result is in the range 0 to 23.
- If the value expression is of the time interval data type, the result will be in the range -99 to 99.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Retrieve all rows with the current time from the rows in column C1 (time data type) of table T1:
SELECT * FROM T1
WHERE HOUR (C1) = HOUR(CURRENT TIME)
(12) INTEGER
(a) Function
The INTEGER scalar function converts numeric data into an integer.
(b) Format
INT[EGER] (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Numeric literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The following items can be specified as the data type of value-expression:
- The data type of the result is an integer (INTEGER).
- The result of executing the INTEGER scalar function must be a value that can be expressed in INTEGER.
- Any numeric digits in the result that follow the decimal point are rounded off.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Obtain only the fractional part from column C1 (DECIMAL(4,3)) in table T1:
SELECT C1 - INTEGER(C1) FROM T1
![[Figure]](figure/zu2s0230.gif)
(13) LENGTH
(a) Function
The LENGTH scalar function determines the length of a value expression.
(b) Format
LENGTH ({value-expression
|GET_JAVA_STORED_ROUTINE_SOURCE specification})
(c) Rules
- The following items can be specified as the value expression:
- Literals
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Date operations
- Time operation
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- : embedded-variable [: indicator-variable] AS data-type (allowable data types: BLOB or BINARY types only)
- ? AS data-type (allowable data types: BLOB or BINARY types only)
- The following data types cannot be specified in value-expression:
- BOOLEAN
- Abstract data type
- When specifying an embedded variable or a ? parameter in value-expression, its data type must be specified in the AS clause. An error may result if the actual length (for a locator, the actual length of the data allocated to the locator) of the data assigned to the embedded variable or ? parameter is greater than the maximum length of the data type specified in the AS clause.
- The data type of the result is integer (INTEGER).
- The execution result varies depending on the data type of the value expression. The following table describes the execution result as a function of the data type of the value expression.
Table 2-35 Execution result as a function of the data type of the value expression for the LENGTH scalar function
Data type of value expression | Execution result |
---|
Fixed-length character data | Defined length in bytes for the default character set. Otherwise, defined length in characters in that character set. |
Variable-length character data | Actual length in bytes for the default character set. Otherwise, actual length in characters in that character set. |
Fixed-length national character data | Definition length in characters |
Variable-length national character data | Actual number of data characters |
Fixed-length mixed character data | Actual number of data characters |
Variable-length mixed character data | Actual number of data characters |
Numeric data | Definition length in bytes For the DECIMAL data type, the length is ( number of digits specified in precision 2 + 1). For details, see 1.2 Data types. |
Date, time, or time stamp data | Definition length in bytes |
Date interval/time interval data | For details, see 1.2 Data types. |
Large-object data | Actual number of data bytes |
Binary data | Actual number of data bytes |
- If the value expression is of the character string data type, a space is counted as one character.
- If the value expression is a literal, it is processed according to the data type interpreted by HiRDB; see 1.4 Literals for details.
- If the value expression is USER, CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP[(p)], the value expression is processed according to the data type as interpreted by HiRDB. For details, see 1.5 USER, CURRENT_DATE value function, CURRENT_TIME value function, and CURRENT_TIMESTAMP value function.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(14) LOWER
(a) Function
The LOWER function converts the uppercase alphabetic characters in character data, national character data, or mixed character data into lowercase characters.
(b) Format
LOWER (value-expression)
(c) Rules
- The following items can be specified as a value expression:
- Literals
- USER
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- NULL, embedded variables, or the ? parameter cannot be specified in the value expression.
- The data type of the value expression must be character string data type (CHAR or VARCHAR), national character string data type (NCHAR or NVARCHAR) or mixed character string data type (MCHAR or MVARCHAR).
- If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- The execution result inherits the data type and the data length of the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
- The character set of the result is the character set of the value expression specified in the argument.
(15) MINUTE
(a) Function
Extracts the minute part from time data, time stamp data, or time interval data.
(b) Format
MINUTE (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Time operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be either the time data type (TIME) or the time interval data type (INTERVAL HOUR TO SECOND).
- The data type of the result is integer (INTEGER).
- If the value expression is the time data type or time stamp data type, the result is in the range 0 to 59.
- If the value expression is of the time interval data type, the result will be in the range -59 to 59.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Retrieve the data for which the difference between column C2 (time data type) and column C3 (time data type) in table T1 is less than or equal to 30 minutes:
SELECT C1 FROM T1
WHERE MINUTE(C3-C2) <= 30
![[Figure]](figure/zu2s0240.gif)
(16) MOD
(a) Function
The MOD scalar function returns the remainder from a division operation.
(b) Format
MOD (value-expression-1, value-expression-2)
(c) Rules
- The following items can be specified as value-expression-1 and value-expression-2:
- Integer literals or decimal number literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The numerator is specified as value-expression-1, and the denominator is specified as value-expression-2.
- The data types of value-expression-1 and value-expression-2 must be one of the following:
- Integer (INTEGER, SMALLINT)
- Fixed-point number (DECIMAL)
- The value of the result is not NOT NULL constrained (the null value is allowed). If value-expression-1 or value-expression-2 is the null value, the result will also be the null value.
- If value-expression-1 or value-expression-2 contains a decimal part, the result value will also contain a decimal part.
- The sign of the result will be the same as the sign of value-expression-1.
- If value-expression-2 is 0, an error results (for the result when overflow error suppression is set, see 2.18 Operational results with overflow error suppression specified).
- If the following inequality holds, overflow will occur during the computation, resulting in an overflow error:
- (p1 - s1) + s2 > max_prec
- p1: Effective precision of the value of value-expression-1
- s1: Effective decimal scaling position of the value of value-expression-1
- p2: Effective precision of the value of value-expression-2
- s2: Effective decimal scaling position of the value of value-expression-2
- max_prec: The following table gives the maximum value max_prec of the precision.
Table 2-36 Maximum value max_prec of the precision
System common definition pd_sql_dec_op_maxprec operand# | p1 and p2 | max_prec value |
---|
29 or omitted | p1 29 and p2 29 | 29 |
p1 > 29 or p2 > 29 | 38 |
38 | Any | 38 |
- #
- For details about the pd_sql_dec_op_maxprec operand of the system common definition, see the manual HiRDB Version 9 System Definition.
For the result when overflow error suppression is set, see 2.18 Operational results with overflow error suppression specified.
- The following table indicates the relationship between a result data type and value-expression-1 and value-expression-2 data types.
Table 2-37 Relationship between a result data type and value-expression-1 and value-expression-2 data types
value-expression-1 Data Type | Value-expression-2 data type |
---|
SMALLINT | INTEGER | DECIMAL |
---|
SMALLINT | SMALLINT | INTEGER | DECIMAL |
INTEGER | SMALLINT | INTEGER | DECIMAL |
DECIMAL (p, 0) | SMALLINT | INTEGER | DECIMAL |
DECIMAL (p, s) (s > 0) | DECIMAL | DECIMAL | DECIMAL |
- The following table gives the resulting precision and decimal scaling position when the data type of a result is DECIMAL.
Table 2-38 Precision and decimal scaling position of a result when the result data type is DECIMAL
Item | Precision or decimal scaling position |
---|
Precision (p) | p=MIN (p2-s2+s, max_prec) |
Decimal scaling position (s) | s=MAX (s1, s2) |
- Note 1
- value-expression-1 data type: DECIMAL(p1,s1)
- value-expression-2 data type: DECIMAL(p2,s2)
- Note 2
- INTEGER is treated as DECIMAL(10,0).
- SMALLINT is treated as DECIMAL(5,0).
- Note 3
- max_prec is the maximum value of the precision as shown in Table 2-36 Maximum value max_prec of the precision.
(17) MONTH
(a) Function
Extracts the month part from date data, time stamp data, or date interval data.
(b) Format
MONTH (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_DATE
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be the date data type (DATE), time stamp data type (TIMESTAMP), or date interval data type (INTERVAL YEAR TO DAY).
- The data type of the result is integer (INTEGER).
- If the value expression is the date data type or time stamp data type, the result is in the range 1 to 12.
- If the value expression is of the date interval data type, the result will be in the range -11 to 11.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Delete all rows from table T1 that are not of the current month (September):
DELETE FROM T1
WHERE MONTH(C1) <> MONTH(CURRENT_DATE)
![[Figure]](figure/zu2s0250.gif)
(18) POSITION
(a) Function
Determines the starting position of the first part in a data string (a character string or binary string) that matches a given data substring.
(b) Format
POSITION (value-expression-1 IN value-expression-2 [ FROM value-expression-3]) |
(c) Rules
- In value-expression-1, specify the search data substring. In value-expression-2, specify the data string to be searched for. Items that can be specified in value-expression-1 and value-expression-2 are listed below. Items that can be specified vary depending upon combinations of the data types of value-expression-1 and value-expression-2. For specifiable combinations, see Rule 2.
- Literals (character strings, national character strings, mixed character strings, or hexadecimal character strings)
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- : embedded-variable [: indicator-variable] AS data-type (allowable data types: BLOB or BINARY types only)
- ? AS data-type (allowable data types: BLOB or BINARY types only)
- The following table indicates the combinations of data types that can be specified in value-expression-1 and value-expression-2:
Table 2-39 Combinations of data types that can be specified in value expression 1 and value expression 2 of the scalar function POSITION
- Legend:
- Y: Specifiable
- R: Specifiable, subject to restrictions
- N: Not specifiable
- Table number: Table of combinations of corresponding item if item is specifiable
- #1
- Can be specified only if value expression-1 is a hexadecimal character string literal.
- #2
- Can be specified only if value expression-2 is a hexadecimal character string literal.
- #3
- If value-expression-1 and value-expression-2 are both character string data types, use the same character set for value-expression-1 and value-expression-2. However, if value-expression-1 is the value expression listed below, it is converted to the character set of value-expression-2:
Character string literal
- #4
- Can only be specified if the character set of the value expression of a character string data type is the default character set.
- The following table indicates the combinations of items that can be specified in value expression-1 and value expression-2.
Table 2-40 Combinations of items that can be specified in value expression 1 and value expression 2 of the scalar function POSITION (where value expression 1 and value expression 2 are both character string data types, national character string data type, mixed character string data type, or BINARY type with a maximum length of 32,000 bytes)
Value expression 1 | Value expression 2 |
---|
Lit | Col spc | Cmp spc | SQL | Con | Set fnc | Scl fnc | CSE | CST | Fnc call | Scl sq | Emb var, ? para# |
---|
Literal | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Column specification | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Component specification | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
SQL variable, SQL parameter | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Concatenation | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Set function | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Scalar function | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
CASE expression | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
CAST specification | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Function call | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Embedded variable, ? parameter# | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
- Legend:
- Y: Specifiable
- Lit: Literal
- Col spc: Column specification
- Cmp spc: Component specification
- SQL: SQL variable, SQL parameter
- Con: Concatenation
- Set fnc: Set function
- Scl fnc: Scalar function
- CSE: CASE expression
- CST: CAST specification
- Fnc call: Function call
- Scl sq: Scalar subquery
- Emb var: Embedded variable
- ? para: ? parameter
- #: Embedded variables and ? parameters can be specified only if they are of the BINARY type.
Table 2-41 Combinations of items that can be specified in value expression 1 and value expression 2 of the scalar function POSITION (where value expression 2 is either the BLOB type or the BINARY type with a maximum length of 32,0001 bytes or greater)
Value expression 1 | Value expression 2 |
---|
Lit | Col spc | Cmp spc | SQL | Con | Set fnc | Scl fnc | CSE | CST | Fnc call | Scl sq | Emb var, ? para |
---|
Literal# | N | Y | N | Y | N | N | N | N | N | N | N | Y |
Column specification | N | N | N | N | N | N | N | N | N | N | N | N |
Component specification | N | N | N | N | N | N | N | N | N | N | N | N |
SQL variable, SQL parameter | N | Y | N | Y | N | N | N | N | N | N | N | Y |
Concatenation | N | N | N | N | N | N | N | N | N | N | N | N |
Set function | N | N | N | N | N | N | N | N | N | N | N | N |
Scalar function | N | N | N | N | N | N | N | N | N | N | N | N |
CASE expression | N | N | N | N | N | N | N | N | N | N | N | N |
CAST specification | N | N | N | N | N | N | N | N | N | N | N | N |
Function call | N | N | N | N | N | N | N | N | N | N | N | N |
Embedded variable, ? parameter | N | Y | N | Y | N | N | N | N | N | N | N | Y |
- Legend:
- Y: Specifiable
- N: Not specifiable
- Lit: Literal
- Col spc: Column specification
- Cmp spc: Component specification
- SQL: SQL variable, SQL parameter
- Con: Concatenation
- Set fnc: Set function
- Scl fnc: Scalar function
- CSE: CASE expression
- CST: CAST specification
- Fnc call: Function call
- Scl sq: Scalar subquery
- Emb var: Embedded variable
- ? para: ? parameter
- #: Literals can be specified only if they are of the character string data type (hexadecimal character string literal).
Table 2-42 Combinations of items that can be specified in value expression 1 and value expression 2 of the scalar function POSITION (where value expression 1 is either the BLOB type or the BINARY type with a maximum length of 32,001 bytes or greater, and value expression 2 is either the character string data type or the BINARY type with a maximum length of 32,000 bytes)
Value expression 1 | Value expression 2 |
---|
Lit | Col spc | Cmp spc | SQL | Con | Set fnc | Scl fnc | CSE | CST | Fnc call | Scl sq | Emb var, ? para# |
---|
Literal | N | N | N | N | N | N | N | N | N | N | N | N |
Column specification | N | N | N | N | N | N | N | N | N | N | N | N |
Component specification | N | N | N | N | N | N | N | N | N | N | N | N |
SQL variable, SQL parameter | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Concatenation | N | N | N | N | N | N | N | N | N | N | N | N |
Set function | N | N | N | N | N | N | N | N | N | N | N | N |
Scalar function | N | N | N | N | N | N | N | N | N | N | N | N |
CASE expression | N | N | N | N | N | N | N | N | N | N | N | N |
CAST specification | N | N | N | N | N | N | N | N | N | N | N | N |
Function call | N | N | N | N | N | N | N | N | N | N | N | N |
Embedded variable, ? parameter | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
- Legend:
- Y: Specifiable
- N: Not specifiable
- Lit: Literal
- Col spc: Column specification
- Cmp spc: Component specification
- SQL: SQL variable, SQL parameter
- Con: Concatenation
- Set fnc: Set function
- Scl fnc: Scalar function
- CSE: CASE expression
- CST: CAST specification
- Fnc call: Function call
- Scl sq: Scalar subquery
- Emb var: Embedded variable
- ? para: ? parameter
- #: Embedded variables and ? parameters can be specified only if they are of the BINARY type.
- When specifying an embedded variable or a ? parameter in value-expression-1 or value-expression-2, specify its data type in the AS clause. An error may result if the actual length (for a locator, the actual length of the data allocated to the locator) of the data assigned to the embedded variable or ? parameter is greater than the maximum length of the data type specified in the AS clause.
- value-expression-3 specifies the search start position. The following shows the relationship between the types and range of values for value-expression-2 and value-expression-3.
- If value-expression-2 is the BLOB type, BINARY type, or a character string data type that uses the default character set
Specify value-expression-3 in bytes with a value in the following range:
If value-expression-2 is a character string data type that uses the default character set, value-expression-3 must be greater than or equal to 1 and less than or equal to the maximum length of value-expression-2.
If value-expression-2 is the BLOB type or BINARY type, value-expression-3 must be greater than or equal to 1.
- If value-expression-2 is a national character data type or mixed character string data type
Specify value-expression-3 in characters. Specify the value of value-expression-3 so that it is greater than or equal to 1 and less than or equal to the maximum length of value-expression-2. For a mixed character string data type, the maximum length of value-expression-2 is in bytes.
- If value-expression-2 is a character string data type that uses a character set other than the default character set
Specify value-expression-3 in characters. Specify the value of value-expression-3 so that it is greater than or equal to 1 and less than or equal to the maximum length of value-expression-2 divided by c. The value of c is the minimum size, in bytes, of characters in the character set being used. This is 1 for EBCDIK and 2 for UTF-16. The maximum length of value-expression-2 is in bytes.
If value-expression-3 is omitted, the search start position is assumed to be 1.
- The following items can be specified in value-expression-3:
- Unsigned integer literal
- Column specification
- Component specification
- Arithmetic operations
- Set function
- Scalar function
- CASE expression
- CAST specification
- Function call
- Scalar subquery
- SQL variable or SQL parameter
- Embedded variable or ? parameter
The following items can be specified in value-expression-3 if value-expression-1 or value-expression-2 is a BLOB type or a BINARY type with a maximum length of 32,0001 bytes or greater:
- Unsigned integer literal
- SQL variable or SQL parameter
- Embedded variable or ? parameter
- The data type of value-expression-3 must be integer (INTEGER, SMALLINT).
- The data type of a result will be integer (INTEGER).
- If value-expression-2 is the BLOB type, BINARY type, or a character string data type that uses the default character set, the result is in bytes. If value-expression-2 is a national character string data type, mixed character string data type, or character string data type that uses a character set other than the default character set, the result is in characters.
- If the actual length of value-expression-1 is 0, the result will be the value of value-expression-3. If value-expression-3 is omitted, the actual length will be 1.
- If the actual length of value-expression-1 is greater than 0 and value-expression-3 is greater than the actual length of value-expression-2, the result will be 0.
- If the data substring of value-expression-1 is not found beyond the starting search position in the data string specified in value-expression-2, the result will be 0.
- The value of the result will be the NOT NULL constraint (allows the null value). If value-expression-1, value-expression-2, or value-expression-3 is the null value, the result will also be the null value.
(d) Example
Find the first position where the character string 'TIME' occurs at byte 6 or beyond in column C1 (data type: CHAR) of table T1:
SELECT POSITION('TIME:' IN C1 FROM 6) FROM T1
![[Figure]](figure/zu2s0255.gif)
(19) SECOND
(a) Function
Extracts the second part from time data, time stamp data, or time interval data.
(b) Format
SECOND (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Time operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be the time data type (TIME), time stamp data type (TIMESTAMP), or time interval data type (INTERVAL HOUR TO SECOND).
- The data type of the result is integer (INTEGER).
- If the value expression is the time data type or time stamp data type, the result is in the range 0 to 59. If the value expression is time data or time stamp data that includes a leap second, the result is in the range of 0 to 61. For details about how to specify time data or time stamp data that includes a leap second, see the description of the pd_leap_second operand in the manual HiRDB Version 9 System Definition.
- If the value expression is of the time interval data type, the result will be in the range -59 to 59.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Determine in units of seconds the difference between the earliest time and the latest time in column C1 (time data type) in table T1:
SELECT MINUTE(MAX(C1)-MIN(C1))
*60+SECOND(MAX(C1)
-MIN(C1))
FROM T1
![[Figure]](figure/zu2s0260.gif)
(20) SUBSTR
(a) Function
Extracts a part of character string data, national character string data, mixed character string data, or binary data.
(b) Format
SUBSTR (value-expression-1, value-expression-2 [, value-expression-3])
(c) Rules
- In value-expression-1, specify the data string (a character string or a binary string) to be processed. The following items can be specified in value-expression-1, subject to variation depending upon the data type of value-expression-1:
- Literals (character strings, national character strings, or mixed character strings)
- Column specification
- Component specification
- SQL variables or ? parameters
- Concatenation operation
- Set functions
- Scalar functions
- CASE expression
- CAST specification
- Function calls
- Scalar subquery
- : embedded-variable [: indicator-variable] AS data-type (allowable data types: BLOB or BINARY types only)
- ? AS data-type (allowable data types: BLOB or BINARY types only)
The following table lists the data types that can be specified.
Table 2-43 Items that can be specified depending on the data type of value expression 1 of the scalar function SUBSTR
Item | Data type of value expression 1 |
---|
Character string data type, national character string data type, mixed character string data type | BINARY type with a maximum length of 32,000 bytes | BLOB type, or BINARY type with a maximum length of 32,001 bytes or greater |
---|
Literal | Y | N | N |
Column specification | Y | Y | Y |
Component specified | Y | Y | N |
SQL variable, SQL parameter | Y | Y | Y |
Concatenation Set function Scalar function CASE expression CAST specification | Y | Y | N |
Function call | Y | Y | Y |
Scalar subquery | Y | Y | N |
Embedded variable ? parameter | N | Y | Y |
- Legend:
- Y: Specifiable
- N: Not specifiable
- Specify one of the following for the data type of value-expression-1:
- Character string data type using any character set (CHAR, VARCHAR)
- National character data type (NCHAR, NVARCHAR)
- Mixed character string data type (MCHAR, MVARCHAR)
- BLOB type or BINARY type
- When specifying an embedded variable or a ? parameter in value-expression-1, specify its data type in the AS clause. An error may result if the actual length (for a locator, the actual length of the data allocated to the locator) of the data assigned to the embedded variable or ? parameter is greater than the maximum length of the data type specified in the AS clause.
- In value-expression-2, specify the starting position of the partial data string to be extracted as a positive integer. The following shows the relationship between the type and range of values for value-expression-1 and value-expression-2.
- If value-expression-1 is the BLOB type, BINARY type, or a character string data type that uses the default character set
Specify value-expression-2 in bytes with a value in the following range:
If f value-expression-1 is a character string data type that uses the default character set, value-expression-2 must be greater than or equal to 1 and less than or equal to the maximum length of value-expression-1.
If value-expression-1 is a BLOB type or BINARY type, value-expression-2 must be greater than or equal to 1.
- If value-expression-1 is a national character data type or mixed character string data type
Specify value-expression-2 in characters. Specify the value of value-expression-2 so that it is greater than or equal to 1 and less than or equal to the maximum length of value-expression-1. For a mixed character string data type, the maximum length of value-expression-1 is in bytes.
- If value-expression-1 is a character string data type that uses a character set other than the default character set
Specify value-expression-2 in characters. Specify the value of value-expression-2 so that it is greater than or equal to 1 and less than or equal to the maximum length of value-expression-1 divided by c. The value of c is the minimum size, in bytes, of characters in the character set being used. This is 1 for EBCDIK and 2 for UTF-16. The maximum length of value-expression-1 is in bytes.
- Specify the length of the data to be removed in value-expression-3 as a positive integer. The following shows the relationship between the type and range of values for value-expression-1 and value-expression-3.
- If value-expression-1 is the BLOB type, BINARY type, or a character string data type that uses the default character set
Specify value-expression-3 in bytes with a value in the following range:
If the data type of value-expression-1 is a character string data type that uses the default character set, then 0
(value-expression-3)
(maximum length of value-expression-1) - (value-expression-2) + 1.
If the data type of value-expression-1 is the BLOB type or BINARY type, value-expression-3 must be greater than or equal to 0.
- If value-expression-1 is a national character data type or mixed character string data type
Specify value-expression-3 in characters with a value such that 0
(value-expression-3)
(maximum length of value-expression-1) - (value-expression-2) + 1. For a mixed character string data type, the maximum length of value-expression-1 is in bytes.
- If value-expression-1 is a character string data type that uses a character set other than the default character set
Specify value-expression-3 in characters with a value such that 0
(value-expression-3)
(maximum length of value-expression-1)
c - (value-expression-2) + 1. The value of c is the minimum size, in bytes, of characters in the character set being used. This is 1 for EBCDIK and 2 for UTF-16. The maximum length of value-expression-1 is in bytes.
A variable of 0 cannot be specified in value-expression-3.
- The following items can be specified as value-expression-2 and value-expression-3:
- Unsigned integer literals
- Column specifications
- Component specification
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- SQL variables or SQL parameters
- Embedded variables or ? parameters
- Scalar subquery
If value-expression-1 is of the BLOB type of the BINARY type with a minimum length of 32,001 bytes, the following items can be specified in value-expression-2 and value-expression-3:
- Unsigned integer literals
- SQL variables or SQL parameters
- Embedded variables or ? parameters
- The data type of value-expression-2 and value-expression-3 must be an integer (INTEGER or SMALLINT).
- If value-expression-2 is greater than the real length of value-expression-1, the result is the null value (the length of the result: 0).
- If the data length of a result is 0, the result is the null value.
- If value-expression-3 is omitted and value-expression-1 is fixed-length data, HiRDB extracts characters from the starting position indicated by value-expression-2 through the last character indicated by the defined length. If value-expression-1 is variable-length data, HiRDB extracts characters from the starting position indicated by value-expression-2 through the last character indicated by the real length.
- If value-expression-1 is of the BLOB type and value-expression-3 is not omitted, and if the data string in the specified range includes a part that does not contain real data, HiRDB only extracts the part that contains real data.
- The value of the result is not NOT NULL constrained (the null value is allowed).
- If value-expression-1, value-expression-2, or value-expression-3 is a null value, the result is also a null value.
- The data types and lengths of results are shown in Table 2-44 to Table 2-47.
Table 2-44 Data types and lengths of results of the SUBSTR scalar function (when the data type of value-expression-1 is a character string type that uses the default character set, national character string type, mixed character string type, BLOB type, or BINARY type and value-expression-3 is specified)
Data type of character string (value-expression-1) from which partial string is extracted | Actual length | Length L (value-expression-3) |
---|
Literal (character string) | Non-literal |
---|
L1 255 | L1 256 |
---|
CHAR(n) | -- | CHAR(L) | VARCHAR(L)[L] | VARCHAR[L] |
VARCHAR(n) | [r] |
NCHAR(n) | -- | NCHAR(L) | NVARCHAR(L)[L] | NVARCHAR[L] |
NVARCHAR(n) | [r] |
MCHAR(n) | -- | MCHAR(L1) | MVARCHAR(L1)[L2] | MVARCHAR[L2] |
MVARCHAR(n) | [r] |
BLOB(n) | [r] | BLOB(L)[k5] | BLOB(L)[k5] | BLOB(n)[k5] |
BINARY(n) | [r] | BINARY(L)[k5] | BINARY(L)[k5] | BINARY(n)[k5] |
- [ ]
- Value enclosed in square brackets is the actual length.
- L1
- If data type is character string: Length L of value-expression-3 (in bytes).
- If data type is national character string: Length L of value-expression-3 (in characters) x 2
- If data type is mixed character string: MIN(length L of value-expression-3 (in characters) x c, n).
- L2
- Length (in bytes) of the extracted partial character string containing L characters (L
L2
L1).
- n
- Definition length of the data string (value-expression-1) to be processed
- For the character string data type, the mixed character string data type, or the BLOB type, in bytes
- For the national character string data type, in units of characters
- k5
- min(L, r - S + 1)
- c
- Maximum number of bytes representing each character
- The following table indicates the maximum number of bytes per character code.
Character code type specifying pdntenv or pdsetup | Maximum number of bytes |
---|
sjis | 2 |
ujis | 2 |
chinese | 2 |
chinese-gb18030 | 4 |
lang-c | 2 |
utf-8# | 3 to 6 |
- #
- When utf-8 is specified for the character code type in the pdntenv command (pdsetup command in the case of UNIX), the following specifications are used:
- pd_substr_length in the system common definition
- PDSUBSTRLEN in the client environment definition
- SUBSTR LENGTH in the SQL compile option
- --: Not applicable.
Note: If value-expression-1 is a variable-length character string, the area where the base data is not included in the substring being removed is filled with spaces of that character set.
- Example:
- Executing SUBSTR(character-string-1, 3, 5) on character string 1 of VARCHAR(8)[5] causes spaces to be set in the two right-side characters in the character string being extracted.
If value-expression-1 is of the BLOB type or the BINARY type and value-expression-3 is not omitted, and if the binary string in the specified range includes a part that does not contain real data, HiRDB extracts only the part that contains real data, without setting spaces.
- Example:
- Executing SUBSTR(binary-data-1, 101, 600) on binary data 1 of BLOB(1024)[512] produces a result that is from bytes 101 to 512 of binary data 1.
Table 2-45 Data types and lengths of results of the SUBSTR scalar function (when the data type of value-expression-1 is a character string type that uses the default character set, national character string type, mixed character string type, BLOB type, or BINARY type and value-expression-3 is omitted
Data type of character string (value-expression-1) from which partial string is extracted | Actual length | Begin position S (value-expression-2) |
---|
Literal (character string) | Non-literal |
---|
value-expression-1: Fixed length | value-expression-1: Variable length |
---|
k0 255 | k0 256 |
---|
CHAR(n) | -- | CHAR(k1) | VARCHAR(n)[k1] | -- | VARCHAR(n)[k1] |
VARCHAR(n) | [r] | -- | -- | VARCHAR(n)[k2] | VARCHAR(n)[k2] |
NCHAR(n) | -- | NCHAR(k1) | NVARCHAR(n)[k1] | -- | NVARCHAR(n) [k1] |
NVARCHAR(n) | [r] | -- | -- | NVARCHAR(n)[k2] | NVARCHAR(n) [k2] |
MCHAR(n) | -- | MCHAR(k1) | MVARCHAR(n) [k3] | -- | MVARCHAR(n) [k3] |
MVARCHAR(n) | [r] | -- | -- | MVARCHAR(n)[k4] | MVARCHAR(n) [k4] |
BLOB(n) | [r] | -- | -- | BLOB(n)[k2] | BLOB(n)[k2] |
BINARY(n) | [r] | -- | -- | BINARY(n)[k2] | BINARY(n)[k2] |
- [ ]
- Value enclosed in square brackets is the actual length.
- n
- If data type is character string: value in bytes
- If data type is mixed character string: value in bytes
- If data type is national character string: value in characters
- k0
- If data type is character string or mixed character string: n - S + 1
- If data type is mixed character string: (n - S + 1) x 2
- k1
- n - S + 1
- k2
- max(r - S + 1, 0)
- k3
- Number of bytes in the partial character string from the Sth character through the nth byte
- max (n - (S - 1) x c, 0)
k3
n - S + 1
- l4
- Number of bytes in the partial character string from the Sth character through the rth byte
- max (r - (S - 1) x c, 0))
k4
max (r - S + 1, 0))
- c
- Maximum number of bytes representing each character
- For details, see c in Legend of Table 2-44 Data types and lengths of the SUBSTR scalar function (when the data type of value-expression-1 is a character string type that uses the default character set, national character string type, mixed character string type, BLOB type, or BINARY type and value-expression-3 is specified).
- --: Not applicable.
Note: When value-expression-1 is a variable-length character string, value-expression-3 is omitted, and l2 or l4 is 0, the result will be the null value.
Table 2-46 Data types and lengths of the SUBSTR scalar function (when the data type of value-expression-1 is a character string type that uses a character set other than the default character set and value-expression-3 is specified)
Data type of character string (value-expression-1) from which the substring is to be extracted | Actual length | Length L (value-expression-3) |
---|
Constant (character string) | Not a constant |
---|
L1 255 | L1 256 |
---|
CHAR(n) | -- | CHAR(L1) | VARCHAR(L1)[L2] | VARCHAR(n)[L2] |
VARCHAR(n) | -- |
- Legend:
- [ ]: Value enclosed in square brackets is the actual length.
- L1
- MIN(length L of value-expression-3 x c, n)
- L2
- Number of bytes in the character substring of the extracted L character (L
L2
L1)
- c
- If the character set of the value expression result is EBCDIK, 1; if UTF-16, 4.
- n
- Definition length (in bytes) of the data column (value-expression-1) for processing
- --
- Not applicable
- Note
- If value-expression-1 is a variable-length character string, the area where the base data is not included in the string being removed is filled with spaces of that character set.
Table 2-47 Data types and lengths of the SUBSTR scalar function (when the data type of value-expression-1 is a character string type that uses a character set other than the default character set and value-expression-3 is omitted)
Data type of character string (value-expression-1) from which the substring is to be extracted | Actual length | Start position S (value-expression-2) |
---|
Constant (character string) | Non-constant |
---|
value-expression-1: Fixed length | value-expression-1: Variable length |
---|
k0 255 | k0 256 |
---|
CHAR(n) | -- | CHAR(k0) | VARCHAR(n)[k1] | -- | VARCHAR(n)[k1] |
VARCHAR(n) | [r] | -- | -- | VARCHAR(n)[k2] | VARCHAR(n)[k2] |
- Legend:
- [ ]: Value enclosed in square brackets is the actual length.
- n
- Definition length (in bytes) of the data column (value-expression-1) for processing
- k0
- n - (S - 1) x c1
- k1
- Number of bytes in the partial character string from the Sth byte through the nth byte MAX(n - (S - 1) x c2,0)
k1
(n - (S - 1) x c1)
- k2
- Number of bytes in the character substring from the Sth byte through the rth byte MAX(r - (S - 1) x c2, 0)
k2
MAX(r - (S - 1) x c1, 0)
- c1
- Minimum number of bytes representing each character
- If the character set of the value expression result is EBCDIK, 1; if UTF-16, 2.
- c2
- Maximum number of bytes representing each character
- If the character set of the value expression result is EBCDIK, 1; if UTF-16, 4.
- --
- Not applicable
- Note
- If value-expression-1 is a variable-length character string, value-expression-3 is omitted, and k2 is 0, the result will be the null value.
(d) Example
Retrieve the rows in which the two characters in column C1 (CHARACTER data type) of table T1 beginning with the second character are 95:
SELECT C1 FROM T1
WHERE SUBSTR(C1,2,2)='95'
![[Figure]](figure/zu2s0270.gif)
(21) TIME
(a) Function
Converts the character string representation of time in a specified format into time data.
(b) Format
TIME (value-expression[,datetime-format])
(c) Rules
- The following items can be specified as the value expression:
- Literals that are character string expressions of the time
- CURRENT_TIME
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Time operations that produce results that are of the time data type
- Concatenation operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The value expression must be in one of the following data types:
- A datetime format specified:
Character string data type with a defined length of 6 to 255 characters (CHAR, VARCHAR). However, if the character set is UTF-16, it is a character string data type with a defined length of 12 to 510 characters (CHAR, VARCHAR).
Mixed character string data type (MCHAR, MVARCHAR)
- A datetime format not specified:
Character string data type with a defined length of eight bytes (CHAR, VARCHAR). However, if the character set is UTF-16, it is a character string data type with a defined length of 16 bytes (CHAR, VARCHAR).
Time data type (TIME)
- The value expression must be a character string representation of time in the format specified in the datetime format. If the datetime format is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- The value expression must be the character string representation of time in the format specified in datetime-format. If datetime-format is omitted, the predefined character string representation of time must be used.
Examples:
Datetime format 'HH-MI-SS'
'13-45-17'
Datetime format omitted
'13:45:17'
- If the value expression is of the time data type, the result will be the equivalent time.
- For datetime formats, see 1.11 Specifying a datetime format.
- The data type of the result is the time data type (TIME).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression or the datetime format is the null value, the result also is the null value.
- When specifying a datetime format, use the same character set for the value expression and datetime format. However, if the datetime format is the value expression listed below, it is converted to the character set of the value expression:
(d) Example
- Obtains the difference between the earliest time and the latest time in column C1 (data type: CHAR) of table T1:
SELECT MAX(TIME(C1))-MIN(TIME(C1))
FROM T1
![[Figure]](figure/zu2s0280.gif)
- Obtains time data from a character string in column C1 (data type: CHAR) of table T2, expressed in a format ('HHMISS') other than the predefined character string representation of time:
SELECT TIME(C1,'HHMISS') FROM T2
![[Figure]](figure/zu2s0520.gif)
(22) TIMESTAMP
(a) Function
- Converts the predefined character expression of a time stamp into time stamp data.
- Converts the cumulative number of days from January 1, year 1 (A.D.) into the equivalent time stamp data.
- Converts date data and time data into time stamp data that is the combination of the two data items.
(b) Format
- Format of function 1:
TIMESTAMP(value-expression) |
- Format of function 2:
TIMESTAMP(value-expression) |
- Format of function 3:
TIMESTAMP(value-expression-1, value-expression-2) |
(c) Rules for function 1
- The following items can be specified in value-expression:
- Time stamp literals in predefined character string representation
- CURRENT_TIMESTAMP[(p)]
- Column specification
- Component specification
- SQL variables or SQL parameters
- Concatenation operation
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The following data types can be specified:
- Character string data type with a defined length of 19 to 26 characters (CHAR, VARCHAR). However, if the character set is UTF-16, it is a character string data type with a defined length of 38 to 52 characters (CHAR, VARCHAR).
- Time stamp data type (TIMESTAMP)
- If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- When specifying a character data type in value-expression, specify the predefined character string representation of a time stamp.
- If value-expression is of the time stamp data type, the result is that time stamp.
- The data type of the result is the time stamp data type (TIMESTAMP) taking the following fractional second precision:
- If value-expression is of the character data type, its fractional second precision is based on the predefined character string representation of the time stamp of the value-expression.
- If value-expression is the time stamp data, its fractional second precision is that of the time stamp data.
(d) Rules for function 2
- The following items can be specified in value-expression:
- Numeric literals
- Column specification
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The data type of value-expression must be an integer (INTEGER). When specifying an arithmetic operation, a scalar function, a CAST expression, a CASE specification, a function call, or a set function, ensure that the result of the operation is an integer data type.
- value-expression must be in the range from 1 to 3,652,059.
- The result is a time stamp after (specified-numeric-value - 1) since January 1, year 1 (A.D.). The time part of the result is 0:0:0.
Example:
If value-expression is 35
February 4, year 1 (A.D.), 0:0:0.
(e) Rules for function 3
- The following items can be specified in value-expression-1:
- Predefined character string representation literals of dates
- CURRENT_DATE
- Column specification
- Component specification
- SQL variables or SQL parameters
- Date operations producing operation results that are of the date data type
- Concatenation operation
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The following data types can be specified in value-expression-1:
- Character string data type with a defined length of 10 bytes (CHAR, VARCHAR). However, if the character set is UTF-16, it is a character string data type with a defined length of 20 bytes (CHAR, VARCHAR).
- Date data type (DATE)
- If value-expression-1 is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- In value-expression-1, specify the predefined character string representation of a date.
Example:
'1995-06-30'
- The following items can be specified in value-expression-2:
- Predefined character string representation literals of time
- CURRENT_TIME
- Column specification
- Component specification
- SQL variables or SQL parameters
- Time operations producing results that are time data
- Concatenation operation
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The following data types can be specified in value-expression-2:
- Character string data type with a defined length of eight bytes (CHAR, VARCHAR). However, if the character set is UTF-16, it is a character string data type with a defined length of 16 bytes (CHAR, VARCHAR).
- Time data type (TIME)
- If value-expression-2 is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- In value-expression-2, specify the predefined character string representation of time.
Example:
'13:45:17'
- Use the same character set for value-expression-1 and value-expression-2. However, if either value-expression-1 or value-expression-2 is the value expression listed below, it is converted to the character set of the corresponding value expression:
(f) Common rules
- The data type of the result is of the time stamp data type (TIMESTAMP).
- The value of the result is not NOT NULL constrained (the null value is allowed). If the value expression is the null value, the result also is the null value.
(g) Examples
- From column C1 (data type: time stamp data type) in table T1, retrieve data occurring since a specified time stamp:
SELECT C1 FROM T1
WHERE C1 >= TIMESTAMP('2000-01-01 00:00:00.00')
![[Figure]](figure/zu2s0530.gif)
- Convert column C1 (data type: numeric data type) in table T1 into time stamp data and insert the results into column C1 (data type: time stamp data type) in table T2:
INSERT INTO T2(C1) SELECT TIMESTAMP(C1) FROM T1
![[Figure]](figure/zu2s0540.gif)
- Combine the data from column C1 (data type: date data type) in table T1 with the data from column C2 (data type: time data type) in table T1, and insert the results into column C1 (data type: time stamp data type) in table T2:
INSERT INTO T2(C1) SELECT TIMESTAMP(C1,C2) FROM T1
![[Figure]](figure/zu2s0550.gif)
(23) TIMESTAMP_FORMAT
(a) Function
Converts the character string representation of a time stamp based on a specified datetime format into time stamp data.
(b) Format
TIMESTAMP_FORMAT(value-expression, datetime-format) |
(c) Rules
- The following items can be specified in value-expression:
- Character string representation literals of time stamps
- Column specification
- Component specification
- SQL variables or SQL parameters
- Concatenation operation
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- Use the following data types for value-expression:
- Character string data type with a defined length of 14 to 255 characters (CHARACTER, VARCHAR). However, if the character set is UTF-16, it is a character string data type with a defined length of 28 to 510 characters (CHARACTER, VARCHAR).
- Mixed character string data type (MCHAR, MVARCHAR)
- If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- In value-expression, specify the character string representation of a time stamp in a format specified in the datetime format.
Example:
Datetime format 'YYYY/MM/DD HH-MI-SS.NNNN':
->'2002/06/30 10-45-30.1523'
- For datetime formats, see 1.11 Specifying a datetime format.
- The data type of the result is of the time stamp data type (TIMESTAMP) with a fractional second precision of 6.
- Specify the same character set in value-expression and datetime-format. However, if datetime-format is the datetime format listed below, it is converted to the character set of the corresponding value expression:
(d) Common rules
- The value of the result is not NOT NULL constrained (the null value is allowed). If the value expression or the datetime format is the null value, the result also is the null value.
(e) Example
Obtain time stamp data from a character string in column C1 (data type: CHAR) in table T1, represented in a format ('DD/MON/YYYY HH-MI-SS NNNN') other than the predefined character string representation of a time stamp:
SELECT TIMESTAMP_FORMAT(C1,'DD/MON/YYYY HH-MI-SS NNNN')
FROM T1
![[Figure]](figure/zu2s0560.gif)
(24) UPPER
(a) Function
Converts the lowercase alphabetic characters in character data, national character data, or mixed character data into uppercase.
(b) Format
UPPER (value-expression)
(c) Rules
- The following items can be specified as a value-expression:
- Literals
- USER
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Concatenation operations
- Set functions
- Scalar functions (HEX, LOWER, SUBSTR, UPPER)
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- NULL, embedded variables, or the ? parameter cannot be specified in the value expression.
- The data type of the value expression must be the character string data type (CHAR or VARCHAR), the national character string data type (NCHAR or NVARCHAR) or the mixed character string data type (MCHAR or MVARCHAR).
- If the data type of the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
- The execution result inherits the data type and the data length of value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
- The character set of the result is the character set of the value expression specified in the argument.
(25) VALUE
(a) Function
The VALUE scalar function extracts the value indicated by the first non-null value expression from a list of value expressions.
(b) Format
VALUE (value-expression [, value-expression]...)
(c) Rules
- The following items can be specified as a value expression:
- Literals
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Date operations
- Time operations
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- ? parameters or embedded variables
- Function call
- Scalar subquery
- The maximum allowable number of value expressions is 255.
- The VALUE scalar function cannot be specified for a value expression if execution of the value expression produces any of the following data types:
- BLOB
- BINARY with a minimum length of 32,001 bytes
- BOOLEAN
- Abstract data type
- NULL cannot be specified in a value expression.
- The ? parameter or an embedded variable cannot be specified alone in the first value expression (including specification in monomial operational expressions).
- All the value expressions must have data types that are compatible for comparison purposes.
Example: If one value expression is the CHAR data type, all other value expressions must also be the CHAR data type.
For data types that can be compared, see 1.2 Data types.
The following data types cannot be compared:
- Date data and a character string expression of date data
- Time data and a character string expression of time data
- Time stamp data and the character string representation of time stamp data
- Date interval data and a decimal expression of date interval data
- Time interval data and a decimal expression of time interval data
- Binary data and hexadecimal character string literals
- If one or more value expressions of VALUE are ? parameters or embedded variables, the data types of the ? parameters or embedded variables will be assumed to be the same as in the first value expression.
- The list of value expressions is evaluated sequentially from left to right. The first value that is not the null value is taken as the result.
- The data type and the data length of the result are the same as data type and data length of the result of a set operation (UNION ALL or EXCEPT ALL). For details, see 2.2 Query expressions.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
- If the value expression is a character string data type, use the same character set for all value expressions. However, if one of the following value expression is specified in the second or later argument, the value expression is converted to the character set of the value expression specified with the first argument.
- Character string literal
- Embedded variable (default character set)
- ? parameter
- If the data type of the result is a character string data type, the character set of the value expression specified in the first argument becomes the character set of the result.
(d) Example
Extract the null value from column C2 of table T5, and assign the value 0:
SELECT VALUE(C1,C2,C3,0)
FROM T5
![[Figure]](figure/zu2s0290.gif)
(26) VARCHAR_FORMAT
(a) Function
Converts date data, time data, or time stamp data into a character string representation according to a specified datetime format.
(b) Format
VARCHAR_FORMAT(value-expression, datetime-format) |
(c) Rules
- The following items can be specified in value-expression:
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specification
- Component specification
- SQL variables or SQL parameters
- Date operations producing results that are of the date data type
- Time operations producing results that are of the time data type
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The data type of value-expression must be the date data type (DATE), time data type (TIME), or time stamp data type (TIMESTAMP).
- For datetime formats, see 1.11 Specifying a datetime format.
- The results take the following data types:
- The datetime format is a character data type (CHAR or VARCHAR):
- VARCHAR(n)
- The datetime format is a mixed character data type (MCHAR or MVARCHAR):
- MVARCHAR(n)
The defined length n takes the following values:
If the value expression is specified as a non-literal value and the datetime format is specified in a literal, the defined length is the maximum length of the character string that can be converted according to a specified format.
If the value expression is specified as a literal and the datetime format is specified in a literal, the defined length is equal to the length of the character string that is converted according to the format.
If the datetime format is specified in a non-literal item, the defined length is equal to definition-length + 15 of the data type of the datetime format. However, if the datetime format is a character string data type that uses the UTF-16 character set, it is converted to the data type of datetime-format + 30.
- The value of the result is the predefined character string representation of the data type of the value expression.
- The value of the result is not NOT NULL constrained (the null value is allowed). If the value expression or the datetime format is the null value, the result also is the null value.
- The character set of the result is the character set of the datetime format specified in the argument.
(d) Example
From column C1 (data type: DATE) in table T1, obtain a result in a character string represented in a specified datetime format ('DD/MON/YYYY'):
SELECT VARCHAR_FORMAT(C1,'DD/MON/YYYY') FROM T1
![[Figure]](figure/zu2s0500.gif)
(27) YEAR
(a) Function
Extracts the year part from date data, time stamp data, or date interval data.
(b) Format
YEAR (value-expression)
(c) Rules
- The following items can be specified in value-expression:
- CURRENT_DATE
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of value-expression must be the date data type (DATE), time stamp data type (TIMESTAMP) or date interval data type (INTERVAL YEAR TO DAY).
- The data type of the result is integer (INTEGER).
- If value-expression is of the date data type or the time stamp data type, the result is 1 to 9999.
- If the value expression is of the date interval data type, the result will be in the range -9999 to 9999. If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Obtain the year of column C1 (date data type) for which the value of column C2 (INTEGER data type) of table T1 is 221140; because column C1 expresses the end of a year, display the result as the year that is one year prior to the year indicated by column C1:
SELECT SUBSTR(DIGITS(YEAR(C1)-1),7,4),N'YEAR' FROM T1
WHERE C2=221140
![[Figure]](figure/zu2s0300.gif)