2.16 Scalar functions

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 three types of scalar functions:

The following table lists the scalar functions.

Table 2-28 List of scalar functions

ClassificationScalar code functionFunctionType of scalar function
Conversion functionINTEGERConverts numeric data into integer data.Built-in
DECIMALConverts numeric data into decimal data.Built-in
FLOATConverts numeric data into floating-point data.Built-in
DIGITSExtracts the numeric part of integer, decimal, date interval, or time interval data and converts it into a character string representation.Built-in
NUMEDITEdits a numeric value and converts it into a character string representation.Defined
STRTONUMConverts the character string representation of a numeric value into a numeric data type.Defined
CHARACTERConverts date data, time data, or time stamp data into a character string representation.Built-in
VARCHAR_FORMATConverts date data, time data, or time stamp data into a character string representation in a specified format.Built-in
DATEConverts 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
DAYSConverts either given date data or time stamp data into a cumulative number of days from January 1, year 1 (A.D.).Built-in
TIMEConverts the character string representation of a given time in a specified format into time data.Built-in
TIMESTAMPConverts 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_FORMATConverts the character string representation of a time stamp in a specified format into time stamp data.Built-in
MIDNIGHTSECONDSDetermines the number of seconds from 00:00:00 a.m. to a specified time value.Defined
HEXConverts a numeric expression into a hexadecimal character string representation.Built-in
ASCIIConverts a given character into its ASCII code.Defined
CHRConverts a given ASCII code into its character equivalent.Defined
RADIANSConverts a given angle from degrees to the equivalent radian measure.Defined
DEGREESConverts a given angle from a radian measure to the equivalent degrees.Defined
CAST specificationConverts value expression data into a specified data type.
For CAST specification, see 2.25 CAST specification.
Not applicable
Extraction functionYEARExtracts the year part from date data, time stamp data, or date interval data.Built-in
MONTHExtracts the month part from date data, time stamp data, or date interval data.Built-in
DAYExtracts the day part from date data, time stamp data, or date interval data.Built-in
HOURExtracts the hour part from time data, time stamp data, or time interval data.Built-in
MINUTEExtracts the minute part from time data, time stamp data, or time interval data.Built-in
SECONDExtracts the second part from time data, time stamp data, or time interval data.Built-in
Mathematical functionABSReturns the absolute value of a given numeric expression.Built-in
MODReturns the remainder of a division.Built-in
CEILDetermines the smallest integer greater than or equal to a given numeric value.Defined
FLOORDetermines the largest integer less than or equal to a given numeric value.Defined
TRUNCTruncates a given numeric value below a specified digit.Defined
ROUNDWhen 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
SIGNDetermines the sign of a given numeric value in terms of 1 (positive), 0, and -1 (negative).Defined
SQRTDetermines the square root of a given numeric value.Defined
POWERDetermines the power of a given numeric value.Defined
EXPDetermines the power to the base of the natural logarithm.Defined
LNDetermines the natural logarithm of a given numeric value.Defined
LOG10Determines the common logarithm of a given numeric value.Defined
SINDetermines the sine (trigonometric function) of an angle specified in radian measure.Defined
COSDetermines the cosine (trigonometric function) of an angle specified in radian measure.Defined
TANDetermines the tangent (trigonometric function) of an angle specified in radian measure.Defined
ASINDetermines the inverse sine (trigonometric function) of an angle specified in radian measure.Defined
ACOSDetermines the inverse cosine (trigonometric function) of an angle specified in radian measure.Defined
ATANDetermines the inverse tangent (trigonometric function) of an angle specified in radian measure.Defined
ATAN2Determines the inverse sine (trigonometric function) of a given point (x, y) in terms of radian measure.Defined
SINHDetermines the hyperbolic sine of a given numeric value.Defined
COSHDetermines the hyperbolic cosine of a given numeric value.Defined
TANHDetermines the hyperbolic tangent of a given numeric value.Defined
PIDetermines the circle ratio [Figure].Defined
Character string manipulation functionSUBSTRDetermines 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
LEFTSTRDetermines a partial character string of a specified number of characters from the beginning of a given character string.Defined
RIGHTSTRDetermines a partial character string of a specified number of characters from the end of a given character string.Defined
UPPERConverts lower case characters into upper case characters in given character string data.Built-in
LOWERConverts 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
LTRIMTrims either spaces or specified characters from the left.Defined
RTRIMTrims either spaces or specified characters from the right.Defined
LTRIMSTRTrims a specified character string from the left.Defined
RTRIMSTRTrims 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
POSSTRDetermines the character position of a specified nth partial character string that occurs after a specified position in a given character string.Defined
POSITIONDetermines 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
REVERSESTRDetermines a right-left reversed character string.Defined
Date manipulation functionNEXT_DAYDetermines the date of a specified day of week following a given date.Defined
LAST_DAYDetermines the last day of the year in which a specified date falls.Defined
DAYOFWEEKGiven a date in day of week, determines the ordinal number of the day in that week.Defined
DAYOFYEARGiven a date, determines the ordinal number of the date in that year.Defined
DAYNAMEDetermines the day of week of a specified date in English.Defined
WEEKGiven a date, determines the ordinal number of the week in which the date falls in that year.Defined
WEEKOFMONTHGiven a date, determines the ordinal number of the week in that month.Defined
MONTHNAMEDetermines the name of the month of a specified date in English.Defined
ROUNDMONTHGiven 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
TRUNCYEARGiven the first month and day of a fiscal year, determines the first date of that fiscal year.Defined
QUARTERGiven the first month and day of a fiscal year, determines the quarter in which that date falls.Defined
HALFGiven the first month and day of a fiscal year, determines whether that date falls in the first half or the second half.Defined
CENTURYDetermines the century of a specified date.Defined
MONTHS_BETWEENDetermines the number of months between given dates as a real number.Defined
YEARS_BETWEENDetermines the number of years between given dates as a real number.Defined
Datetime manipulation functionDATE_TIMEConcatenates date data and time data, and converts the result into a predefined character string representation of a time stamp.Defined
INTERVAL_DATETIMESDetermines the date and time interval between time stamps that are given in a predefined character string representations.Defined
ADD_INTERVALAdds a given date and time interval to a date stamp in a predefined character string representation.Defined
Inspection functionISDIGITSDetermines whether all characters in a given character string are digits.Defined
IS_DBLBYTESDetermines whether all characters in a given character string are double-byte characters.Defined
IS_SNGLBYTESDetermines whether all characters in a given character string are single-byte characters.Defined
XML type value manipulation functionXMLQUERYEvaluates XQuery expressions and generates an XML type value as the result.Plug-in
XMLSERIALIZEGenerates a VARCHAR or BINARY type value from an XML type value.Plug-in
XMLPARSEGenerates an XML type value from an XML document.Plug-in
Other functionsLENGTHDetermines the data length of a value expression.Built-in
VALUEExtracts the first non-null value expression from a list of value expressions.Built-in
GREATESTDetermines the maximum value of arguments.Defined
LEASTDetermines the minimum value of arguments.Defined
BIT_AND_TESTDetermines the logical product of specified arguments bit-by-bit and returns the result in terms of true or false.Built-in
CASE expressionsSpecifies a conditional value.
For CASE expressions, see 2.17 CASE expressions.
Not applicable
Legend:
Built-in: System built-in scalar function
Defined: System-defined scalar function
Plug-in: Plug-in defined scalar function
Organization of this section
2.16.1 System built-in scalar functions
2.16.2 System-defined scalar functions
2.16.3 Plug-in definition scalar functions