![[Contents]](FIGURE/CONTENT.GIF)
Scalable Database Server, HiRDB Version 8 SQL Reference
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
![[Next]](FIGURE/AFTER.GIF)
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 ![[Figure]](FIGURE/ZUENG046.GIF) . |
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 |
All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.