Scalar functions are used as data-type variables, for the partial extraction of data, and for value conversions; they are specified in selection expressions in an SQL query or in search conditions.
HiRDB provides the following two types of scalar functions:
Table 2-23 shows a list of scalar functions.
Table 2-23 List of scalar functions
Classification | Scalar code function | Function | Type of scalar function |
---|---|---|---|
Conversion function | INTEGER | Converts numeric data into integer data. | Built-in |
DECIMAL | Converts numeric data into decimal data. | Built-in | |
FLOAT | Converts numeric data into floating-point data. | Built-in | |
DIGITS | Extracts the numeric part of integer, decimal, date interval, or time interval data and converts it into a character string representation. | Built-in | |
NUMEDIT | Edits a numeric value and converts it into a character string representation. | Defined | |
STRTONUM | Converts the character string representation of a numeric value into a numeric data type. | Defined | |
CHARACTER | Converts date data, time data, or time stamp data into a character string representation. | Built-in | |
VARCHAR_FORMAT | Converts date data, time data, or time stamp data into a character string representation in a specified format. | Built-in | |
DATE | Converts the character string representation of a date in a specified format into date data. Converts a given cumulative number of days from January 1, year 1 (A.D.) into date data representing that date. | Built-in | |
DAYS | Converts either given date data or time stamp data into a cumulative number of days from January 1, year 1 (A.D.). | Built-in | |
TIME | Converts the character string representation of a given time in a specified format into time data. | Built-in | |
TIMESTAMP | Converts the predefined character string representation of a time stamp into the time stamp data. Converts the cumulative number of days from January 1, year 1 (A.D.) into time stamp data that it represents. Given date data and time data, converts the results into time stamp data combining the data items. | Built-in | |
TIMESTAMP_FORMAT | Converts the character string representation of a time stamp in a specified format into time stamp data. | Built-in | |
MIDNIGHTSECONDS | Determines the number of seconds from 00:00:00 a.m. to a specified time value. | Defined | |
HEX | Converts a numeric expression into a hexadecimal character string representation. | Built-in | |
ASCII | Converts a given character into its ASCII code. | Defined | |
CHR | Converts a given ASCII code into its character equivalent. | Defined | |
RADIANS | Converts a given angle from degrees to the equivalent radian measure. | Defined | |
DEGREES | Converts a given angle from a radian measure to the equivalent degrees. | Defined | |
CAST specification | Converts value expression data into a specified data type. For CAST specification, see 2.25 CAST specification. | Not applicable | |
Extraction function | YEAR | Extracts the year part from date data, time stamp data, or date interval data. | Built-in |
MONTH | Extracts the month part from date data, time stamp data, or date interval data. | Built-in | |
DAY | Extracts the day part from date data, time stamp data, or date interval data. | Built-in | |
HOUR | Extracts the hour part from time data, time stamp data, or time interval data. | Built-in | |
MINUTE | Extracts the minute part from time data, time stamp data, or time interval data. | Built-in | |
SECOND | Extracts the second part from time data, time stamp data, or time interval data. | Built-in | |
Mathematical function | ABS | Returns the absolute value of a given numeric expression. | Built-in |
MOD | Returns the remainder of a division. | Built-in | |
CEIL | Determines the smallest integer greater than or equal to a given numeric value. | Defined | |
FLOOR | Determines the largest integer less than or equal to a given numeric value. | Defined | |
TRUNC | Truncates a given numeric value below a specified digit. | Defined | |
ROUND | When a boundary between rounding up and rounding off is specified, rounds a given numeric value to a specified number of digits, or performs rounding. | Defined | |
SIGN | Determines the sign of a given numeric value in terms of 1 (positive), 0, and -1 (negative). | Defined | |
SQRT | Determines the square root of a given numeric value. | Defined | |
POWER | Determines the power of a given numeric value. | Defined | |
EXP | Determines the power to the base of the natural logarithm. | Defined | |
LN | Determines the natural logarithm of a given numeric value. | Defined | |
LOG10 | Determines the common logarithm of a given numeric value. | Defined | |
SIN | Determines the sine (trigonometric function) of an angle specified in radian measure. | Defined | |
COS | Determines the cosine (trigonometric function) of an angle specified in radian measure. | Defined | |
TAN | Determines the tangent (trigonometric function) of an angle specified in radian measure. | Defined | |
ASIN | Determines the inverse sine (trigonometric function) of an angle specified in radian measure. | Defined | |
ACOS | Determines the inverse cosine (trigonometric function) of an angle specified in radian measure. | Defined | |
ATAN | Determines the inverse tangent (trigonometric function) of an angle specified in radian measure. | Defined | |
ATAN2 | Determines the inverse sine (trigonometric function) of a given point (x, y) in terms of radian measure. | Defined | |
SINH | Determines the hyperbolic sine of a given numeric value. | Defined | |
COSH | Determines the hyperbolic cosine of a given numeric value. | Defined | |
TANH | Determines the hyperbolic tangent of a given numeric value. | Defined | |
PI | Determines the circle ratio ![]() | Defined | |
Character string manipulation function | SUBSTR | Determines a partial data string of a specified number of characters or length from a specified position in a given data string (character string or binary string). | Built-in |
LEFTSTR | Determines a partial character string of a specified number of characters from the beginning of a given character string. | Defined | |
RIGHTSTR | Determines a partial character string of a specified number of characters from the end of a given character string. | Defined | |
UPPER | Converts lower case characters into upper case characters in given character string data. | Built-in | |
LOWER | Converts upper case characters into lower case characters in given character string data. | Built-in | |
TRANSL (TRANSL_LONG) | Translates a specified character in a character string into another, equivalent character. | Defined | |
LTRIM | Trims either spaces or specified characters from the left. | Defined | |
RTRIM | Trims either spaces or specified characters from the right. | Defined | |
LTRIMSTR | Trims a specified character string from the left. | Defined | |
RTRIMSTR | Trims a specified character string from the right. | Defined | |
REPLACE (REPLACE_LONG) | Repeatedly replaces partial character strings in a given character string with another character string. | Defined | |
INSERTSTR (INSERTSTR_LONG) | Deletes a partial character string of a specified number of characters from a specified position, and inserts another character string into that position. | Defined | |
POSSTR | Determines the character position of a specified nth partial character string that occurs after a specified position in a given character string. | Defined | |
POSITION | Determines the position of the first data substring that occurs at a specified position or beyond in a data string (either a character string or a binary string). | Built-in | |
REVERSESTR | Determines a right-left reversed character string. | Defined | |
Date manipulation function | NEXT_DAY | Determines the date of a specified day of week following a given date. | Defined |
LAST_DAY | Determines the last day of the year in which a specified date falls. | Defined | |
DAYOFWEEK | Given a date in day of week, determines the ordinal number of the day in that week. | Defined | |
DAYOFYEAR | Given a date, determines the ordinal number of the date in that year. | Defined | |
DAYNAME | Determines the day of week of a specified date in English. | Defined | |
WEEK | Given a date, determines the ordinal number of the week in which the date falls in that year. | Defined | |
WEEKOFMONTH | Given a date, determines the ordinal number of the week in that month. | Defined | |
MONTHNAME | Determines the name of the month of a specified date in English. | Defined | |
ROUNDMONTH | Given a smallest number of days beyond which days are to be rounded up, determines the year and month of a specified date by rounding the day. | Defined | |
TRUNCYEAR | Given the first month and day of a fiscal year, determines the first date of that fiscal year. | Defined | |
QUARTER | Given the first month and day of a fiscal year, determines the quarter in which that date falls. | Defined | |
HALF | Given the first month and day of a fiscal year, determines whether that date falls in the first half or the second half. | Defined | |
CENTURY | Determines the century of a specified date. | Defined | |
MONTHS_BETWEEN | Determines the number of months between given dates as a real number. | Defined | |
YEARS_BETWEEN | Determines the number of years between given dates as a real number. | Defined | |
Datetime manipulation function | DATE_TIME | Concatenates date data and time data, and converts the result into a predefined character string representation of a time stamp. | Defined |
INTERVAL_DATETIMES | Determines the date and time interval between time stamps that are given in a predefined character string representations. | Defined | |
ADD_INTERVAL | Adds a given date and time interval to a date stamp in a predefined character string representation. | Defined | |
Inspection function | ISDIGITS | Determines whether all characters in a given character string are digits. | Defined |
IS_DBLBYTES | Determines whether all characters in a given character string are double-byte characters. | Defined | |
IS_SNGLBYTES | Determines whether all characters in a given character string are single-byte characters. | Defined | |
Other functions | LENGTH | Determines the data length of a value expression. | Built-in |
VALUE | Extracts the first non-null value expression from a list of value expressions. | Built-in | |
GREATEST | Determines the maximum value of arguments. | Defined | |
LEAST | Determines the minimum value of arguments. | Defined | |
IS_USER_CONTAINED_IN_HDS_GROUP | Returns the result of a test determining whether a given user belongs to a directory server group or a role. IS_USER_CONTAINED_IN_HDS_GROUP is in effect only when the directory server linkage facility is being used. | Built-in | |
BIT_AND_TEST | Determines the logical product of specified arguments bit-by-bit and returns the result in terms of true or false. | Built-in | |
CASE expressions | Specifies a conditional value. For CASE expressions, see 2.17 CASE expressions. | Not applicable |