2.16.1 System built-in scalar functions

This subsection explains the syntax of system built-in scalar functions.

The following figure shows examples of executing system built-in scalar functions with respect to date data, time data, time stamp data, and numeric data.

Figure 2-6 Execution examples of system built-in scalar functions with respect to date data, time data, time stamp data, and numeric data

[Figure]

[Figure]

Common rules

The following rules apply to system built-in scalar functions:

  1. An embedded variable or the ? parameter cannot be specified in a value expression by itself. However, they can be specified in value expressions involving arithmetic operations (except for unary operators).
  2. When a repetition column is specified as a value expression in an argument, a subscript must also be specified; however, the ANY subscript cannot be specified.
Organization of this subsection
(1) ABS
(2) BIT_AND_TEST
(3) CHARACTER
(4) DATE
(5) DAY
(6) DAYS
(7) DECIMAL
(8) DIGITS
(9) FLOAT
(10) HEX
(11) HOUR
(12) INTEGER
(13) LENGTH
(14) LOWER
(15) MINUTE
(16) MOD
(17) MONTH
(18) POSITION
(19) SECOND
(20) SUBSTR
(21) TIME
(22) TIMESTAMP
(23) TIMESTAMP_FORMAT
(24) UPPER
(25) VALUE
(26) VARCHAR_FORMAT
(27) YEAR

(1) ABS

(a) Function

The ABS scalar function returns the absolute value of a value expression.

(b) Format

ABS(value-expression)

(c) Rules
  1. 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
  2. The data type of a value expression must be numeric data, date interval data, or time interval data.
  3. The data type of the result will be the same as the data type of the value expression.
  4. 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.
  5. 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
  1. 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
  2. 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 1Data type of value expression 2
    Character data (CHAR and VARCHAR)Binary data (BINARY)
    Character data (CHAR and VARCHAR)YN#
    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.
  1. Value expressions consisting solely of embedded variables or ? parameters cannot be specified in both value-expression-1and value-expression-2.
  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.
  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 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
  4. 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.
  5. The data type of the result is the BOOLEAN type.
  6. 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.
  7. 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.
  8. 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:

(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
  1. 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
  2. The data type of value-expression must be the date data type (DATE), time data type (TIME), or time stamp data type (TIMESTAMP).
  3. 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)
  4. The value of the result is a predefined character string representation of the data type of value-expression.
  5. 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.
  6. The character set of the result is the default character set.
(d) Examples

(4) DATE

(a) Function
The DATE scalar function performs the following conversions:
  1. Converts the character string representation of a date in a specified format into date data.
  2. 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
  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
  2. The value expression must be one of the following data types:
    • A datetime format is specified:
      [Figure]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.
      [Figure]Mixed character string data type (MCHAR, MVARCHAR)
    • A datetime format is not specified:
      [Figure]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).
      [Figure]Date data type (DATE)
  3. If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
  4. 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'[Figure] '1995/06/30'
    Datetime format omitted [Figure] '1995-06-30'
  5. If the value expression is of the date data type, the result will be the equivalent date.
  6. For datetime formats, see 1.11 Specifying a datetime format.
  7. 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:
    • Character string literal
(d) Rules for function 2
  1. 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
  2. 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.
  3. The allowable range of values is 1 to 3652059.
  4. 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
  1. The data type of the result is the date data type (DATE).
  2. 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
  1. 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)

  2. 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]

(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
  1. 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
  2. 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).
  3. The data type of the result is integer (INTEGER).
  4. 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.
  5. 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.
  6. 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
  1. 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
  2. The data type of the value expression must be the date data type (DATE) or time stamp data type (TIMESTAMP).
  3. The data type of the result must be an integer (INTEGER).
  4. 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).
  5. 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
  1. 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
  2. The following items can be specified as the data type of value-expression:
    • Numeric data type
  3. 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 TypePrecision (number of digit positions)
    INTEGER10
    SMALLINT5
    DECIMAL15
    FLOATWhen 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.
  4. 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.
  5. The following table lists the data type of the result.

    Table 2-31 Data type of the result of the DECIMAL scalar function

    Data TypePrecision of resultDecimal scaling position of the result
    DECIMALPrecision 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.
  6. 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.
  7. Any digits following the specified decimal scaling position in the result are rounded off.
  8. 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]

(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
  1. 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
  2. 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)
  3. The data type of the result is a fixed-length character string (CHAR).
  4. 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 typeData length of result
    INTEGER10
    SMALLINT5
    DECIMAL(p,s)p
    INTERVAL YEAR TO DAY8
    INTERVAL HOUR TO SECOND6
    p: Precision.
    s: Decimal scaling position.
  5. 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.  [Figure] '0150'
    -12.4 [Figure] '0124'

  6. 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.
  7. 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]

(9) FLOAT

(a) Function

The FLOAT scalar function converts numeric data into floating-point data.

(b) Format

FLOAT (value-expression)

(c) Rules
  1. 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
  2. The following items can be specified as the data type of value-expression:
    • Numeric data type
  3. The data type of the result must be a double-precision floating-point number (FLOAT).
  4. 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]

(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
  1. 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 expressionInternal representation formatHEX(value-expression)
    '#AB12'Data type: CHAR(5)
    23|41|42|31|32
    '2341423132​'
    1234Data 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'
  2. 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
  3. 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 expressionExecution result
    Data typeDefined lengthActual lengthData typeDefined lengthActual length
    CHAR(n)1[Figure] n < 128--CHARn*2--
    128 [Figure] n [Figure]16,000VARCHARn*2
    NCHAR(n)1 [Figure] n < 64CHARn*4--
    64 [Figure] n[Figure] 8,000VARCHARn*4
    MCHAR(n)1 [Figure] n < 128CHARn*2--
    128 [Figure] n[Figure] 16,000VARCHARn*2
    VARCHAR(n)1 [Figure] n[Figure] 16,000[r]VARCHARn*2r*2
    NVARCHAR(n)1 [Figure] n[Figure] 8,000n*4r*4
    MVARCHAR(n)1 [Figure] n[Figure] 16,000n*2r*2
    INTEGER----CHAR8--
    SMALLINT4
    DECIMAL(P,S)1 [Figure] P [Figure]38
    0 [Figure] S[Figure] 38
    S[Figure]P
    ([Figure]P/2[Figure] + 1) * 2
    FLOAT--16
    SMALLFLT8
    DATE8
    TIME6
    TIMESTAMP(p)p = 0, 2, 4, or 6(7+p/2)*2
    INTERVAL YEAR TO DAY--10
    INTERVAL HOUR TO SECOND8
    BINARY(n)1[Figure] n[Figure] 16,000[r]VARCHARn*2r*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.
  4. If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
  5. 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
  6. A value expression that contains embedded variables or ? parameters cannot be specified.
  7. 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.
  8. 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.
  9. 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.
  10. 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.
  11. 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
  1. 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
  2. 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).
  3. The data type of the result is integer (INTEGER).
  4. 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.
  5. 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.
  6. 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
  1. 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
  2. The following items can be specified as the data type of value-expression:
    • Numeric data type
  3. The data type of the result is an integer (INTEGER).
  4. The result of executing the INTEGER scalar function must be a value that can be expressed in INTEGER.
  5. Any numeric digits in the result that follow the decimal point are rounded off.
  6. 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]

(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
  1. 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)
  2. The following data types cannot be specified in value-expression:
    • BOOLEAN
    • Abstract data type
  3. 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.
  4. The data type of the result is integer (INTEGER).
  5. 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 expressionExecution result
    Fixed-length character dataDefined length in bytes for the default character set.
    Otherwise, defined length in characters in that character set.
    Variable-length character dataActual length in bytes for the default character set.
    Otherwise, actual length in characters in that character set.
    Fixed-length national character dataDefinition length in characters
    Variable-length national character dataActual number of data characters
    Fixed-length mixed character dataActual number of data characters
    Variable-length mixed character dataActual number of data characters
    Numeric dataDefinition length in bytes
    For the DECIMAL data type, the length is ([Figure]number of digits specified in precision [Figure] 2[Figure] + 1). For details, see 1.2 Data types.
    Date, time, or time stamp dataDefinition length in bytes
    Date interval/time interval dataFor details, see 1.2 Data types.
    Large-object dataActual number of data bytes
    Binary dataActual number of data bytes
  6. If the value expression is of the character string data type, a space is counted as one character.
  7. If the value expression is a literal, it is processed according to the data type interpreted by HiRDB; see 1.4 Literals for details.
  8. 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.
  9. 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
  1. 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
  2. NULL, embedded variables, or the ? parameter cannot be specified in the value expression.
  3. 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).
  4. If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
  5. The execution result inherits the data type and the data length of the value expression.
  6. 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.
  7. 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
  1. 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
  2. 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).
  3. The data type of the result is integer (INTEGER).
  4. If the value expression is the time data type or time stamp data type, the result is in the range 0 to 59.
  5. 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.
  6. 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]

(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
  1. 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
  2. The numerator is specified as value-expression-1, and the denominator is specified as value-expression-2.
  3. The data types of value-expression-1 and value-expression-2 must be one of the following:
    • Integer (INTEGER, SMALLINT)
    • Fixed-point number (DECIMAL)
  4. 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.
  5. If value-expression-1 or value-expression-2 contains a decimal part, the result value will also contain a decimal part.
  6. The sign of the result will be the same as the sign of value-expression-1.
  7. 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).
  8. 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 p2max_prec value
    29 or omittedp1[Figure] 29 and p2[Figure] 2929
    p1 > 29 or p2 > 2938
    38Any38
    #
    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.
  9. 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 TypeValue-expression-2 data type
    SMALLINTINTEGERDECIMAL
    SMALLINTSMALLINTINTEGERDECIMAL
    INTEGERSMALLINTINTEGERDECIMAL
    DECIMAL (p, 0)SMALLINTINTEGERDECIMAL
    DECIMAL (p, s) (s > 0)DECIMALDECIMALDECIMAL
  10. 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

    ItemPrecision 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
  1. 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
  2. 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).
  3. The data type of the result is integer (INTEGER).
  4. If the value expression is the date data type or time stamp data type, the result is in the range 1 to 12.
  5. 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.
  6. 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]

(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
  1. 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)
  2. 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

    Value expression 1Value expression 2
    Character string data typeNational character string data typeMixed character string data typeBLOB typeBINARY type with a maximum length of 32,000 bytesBINARY type with a maximum length of 32,001 bytes or greater
    Character string data typeY#3
    (Table 2-40)
    NY#4
    (Table 2-40)
    R#1
    (Table 2-41)
    R#1
    (Table 2-40)
    R#1
    (Table 2-41)
    National character string data typeNY
    (Table 2-40)
    NNNN
    Mixed character string data typeY#4
    (Table 2-40)
    NY
    (Table 2-40)
    NNN
    BLOB typeR#2
    (Table 2-42)
    NNY
    (Table 2-41)
    Y
    (Table 2-42)
    Y
    (Table 2-41)
    BINARY type with a maximum length of 32,000 bytesR#2
    (Table 2-40)
    NNY
    (Table 2-41)
    Y
    (Table 2-40)
    Y
    (Table 2-41)
    BINARY type with a maximum length of 32,001 bytes or greaterR#2
    (Table 2-42)
    NNY
    (Table 2-41)
    Y
    (Table 2-42)
    Y
    (Table 2-41)
    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:
    [Figure]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.
  3. 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 1Value expression 2
    LitCol spcCmp spcSQLConSet fncScl fncCSECSTFnc callScl sqEmb var, ? para#
    LiteralYYYYYYYYYYYY
    Column specificationYYYYYYYYYYYY
    Component specificationYYYYYYYYYYYY
    SQL variable, SQL parameterYYYYYYYYYYYY
    ConcatenationYYYYYYYYYYYY
    Set functionYYYYYYYYYYYY
    Scalar functionYYYYYYYYYYYY
    CASE expressionYYYYYYYYYYYY
    CAST specificationYYYYYYYYYYYY
    Function callYYYYYYYYYYYY
    Embedded variable,
    ? parameter#
    YYYYYYYYYYYY
    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 1Value expression 2
    LitCol spcCmp spcSQLConSet fncScl fncCSECSTFnc callScl sqEmb var, ? para
    Literal#NYNYNNNNNNNY
    Column specificationNNNNNNNNNNNN
    Component specificationNNNNNNNNNNNN
    SQL variable,
    SQL parameter
    NYNYNNNNNNNY
    ConcatenationNNNNNNNNNNNN
    Set functionNNNNNNNNNNNN
    Scalar functionNNNNNNNNNNNN
    CASE expressionNNNNNNNNNNNN
    CAST specificationNNNNNNNNNNNN
    Function callNNNNNNNNNNNN
    Embedded variable,
    ? parameter
    NYNYNNNNNNNY
    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 1Value expression 2
    LitCol spcCmp spcSQLConSet fncScl fncCSECSTFnc callScl sqEmb var, ? para#
    LiteralNNNNNNNNNNNN
    Column specificationNNNNNNNNNNNN
    Component specificationNNNNNNNNNNNN
    SQL variable,
    SQL parameter
    YYYYYYYYYYYY
    ConcatenationNNNNNNNNNNNN
    Set functionNNNNNNNNNNNN
    Scalar functionNNNNNNNNNNNN
    CASE expressionNNNNNNNNNNNN
    CAST specificationNNNNNNNNNNNN
    Function callNNNNNNNNNNNN
    Embedded variable,
    ? parameter
    YYYYYYYYYYYY
    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.
  4. 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.
  5. 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:
      [Figure]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.
      [Figure]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.
  6. 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
  7. The data type of value-expression-3 must be integer (INTEGER, SMALLINT).
  8. The data type of a result will be integer (INTEGER).
  9. 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.
  10. 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.
  11. 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.
  12. 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.
  13. 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]

(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
  1. 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
  2. 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).
  3. The data type of the result is integer (INTEGER).
  4. 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.
  5. 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.
  6. 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]

(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
  1. 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

    ItemData 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 bytesBLOB type,
    or BINARY type with a maximum length of 32,001 bytes or greater
    LiteralYNN
    Column specificationYYY
    Component specifiedYYN
    SQL variable,
    SQL parameter
    YYY
    Concatenation
    Set function
    Scalar function
    CASE expression
    CAST specification
    YYN
    Function callYYY
    Scalar subqueryYYN
    Embedded variable
    ? parameter
    NYY
Legend:
Y: Specifiable
N: Not specifiable
  1. 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
  2. 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.
  3. 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:
      [Figure]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.
      [Figure]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.
  4. 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:
      [Figure]If the data type of value-expression-1 is a character string data type that uses the default character set, then 0 [Figure] (value-expression-3) [Figure] (maximum length of value-expression-1) - (value-expression-2) + 1.
      [Figure]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 [Figure] (value-expression-3) [Figure] (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 [Figure] (value-expression-3) [Figure] (maximum length of value-expression-1) [Figure] 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.
  5. 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
  6. The data type of value-expression-2 and value-expression-3 must be an integer (INTEGER or SMALLINT).
  7. 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).
  8. If the data length of a result is 0, the result is the null value.
  9. 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.
  10. 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.
  11. The value of the result is not NOT NULL constrained (the null value is allowed).
  12. If value-expression-1, value-expression-2, or value-expression-3 is a null value, the result is also a null value.
  13. 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 extractedActual lengthLength L (value-expression-3)
    Literal (character string)Non-literal
    L1[Figure] 255L1[Figure] 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[Figure] L2[Figure] 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 pdsetupMaximum number of bytes
sjis2
ujis2
chinese2
chinese-gb180304
lang-c2
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 extractedActual lengthBegin position S (value-expression-2)
Literal (character string)Non-literal
value-expression-1: Fixed lengthvalue-expression-1: Variable length
k0[Figure] 255k0[Figure] 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) [Figure] k3 [Figure]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)) [Figure] k4[Figure] 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 extractedActual lengthLength L (value-expression-3)
Constant (character string)Not a constant
L1[Figure] 255L1[Figure] 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[Figure] L2[Figure] 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 extractedActual lengthStart position S (value-expression-2)
Constant (character string)Non-constant
value-expression-1: Fixed lengthvalue-expression-1: Variable length
k0[Figure] 255k0[Figure] 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) [Figure] k1[Figure] (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) [Figure] k2[Figure] 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]

(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
  1. 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
  2. The value expression must be in one of the following data types:
    • A datetime format specified:
      [Figure]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).
      [Figure]Mixed character string data type (MCHAR, MVARCHAR)
    • A datetime format not specified:
      [Figure]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).
      [Figure]Time data type (TIME)
  3. 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.
  4. 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'[Figure] '13-45-17'
    Datetime format omitted [Figure] '13:45:17'
  5. If the value expression is of the time data type, the result will be the equivalent time.
  6. For datetime formats, see 1.11 Specifying a datetime format.
  7. The data type of the result is the time data type (TIME).
  8. 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.
  9. 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:
    • Character string literal
(d) Example
  1. 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]

  2. 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]

(22) TIMESTAMP

(a) Function
  1. Converts the predefined character expression of a time stamp into time stamp data.
  2. Converts the cumulative number of days from January 1, year 1 (A.D.) into the equivalent time stamp data.
  3. 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
  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
  2. 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)
  3. If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
  4. When specifying a character data type in value-expression, specify the predefined character string representation of a time stamp.
  5. If value-expression is of the time stamp data type, the result is that time stamp.
  6. 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
  1. 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
  2. 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.
  3. value-expression must be in the range from 1 to 3,652,059.
  4. 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 [Figure] February 4, year 1 (A.D.), 0:0:0.
(e) Rules for function 3
  1. 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
  2. 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)
  3. If value-expression-1 is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
  4. In value-expression-1, specify the predefined character string representation of a date.
    Example:
    '1995-06-30'
  5. 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
  6. 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)
  7. If value-expression-2 is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
  8. In value-expression-2, specify the predefined character string representation of time.
    Example:
    '13:45:17'
  9. 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:
    • Character string literal
(f) Common rules
  1. The data type of the result is of the time stamp data type (TIMESTAMP).
  2. 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
  1. 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]

  2. 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]

  3. 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]

(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
  1. 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
  2. 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)
  3. If the value expression is a character string data type (CHAR, VARCHAR), specifying the character set of the data type is optional.
  4. 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'
  5. For datetime formats, see 1.11 Specifying a datetime format.
  6. The data type of the result is of the time stamp data type (TIMESTAMP) with a fractional second precision of 6.
  7. 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:
    • Character string literal
(d) Common rules
  1. 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]

(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
  1. 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
  2. NULL, embedded variables, or the ? parameter cannot be specified in the value expression.
  3. 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).
  4. 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.
  5. The execution result inherits the data type and the data length of value expression.
  6. 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.
  7. 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
  1. 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
  2. The maximum allowable number of value expressions is 255.
  3. 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
  4. NULL cannot be specified in a value expression.
  5. The ? parameter or an embedded variable cannot be specified alone in the first value expression (including specification in monomial operational expressions).
  6. 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
  7. 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.
  8. 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.
  9. 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.
  10. 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.
  11. 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
  12. 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]

(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
  1. 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
  2. The data type of value-expression must be the date data type (DATE), time data type (TIME), or time stamp data type (TIMESTAMP).
  3. For datetime formats, see 1.11 Specifying a datetime format.
  4. 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:
    [Figure]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.
    [Figure]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.
    [Figure]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.
  5. The value of the result is the predefined character string representation of the data type of the value expression.
  6. 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.
  7. 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]

(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
  1. 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
  2. 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).
  3. The data type of the result is integer (INTEGER).
  4. If value-expression is of the date data type or the time stamp data type, the result is 1 to 9999.
  5. 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.
  6. 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]