B. List of Functions

The following table provides a list of functions.

Table B‒1: List of functions
No.	Function			Use
1	Set functions	`MAX`		Determine the maximum value.
2		`MIN`		Determine the minimum value.
3		`SUM`		Determine the sum of the retrieved values.
4		`AVG`		Determine the average of the retrieved values.
5		`COUNT`		Determine the row count (number of results).
6		`COUNT(*)`		Determine the row count (number of results).
7		`STDDEV_POP`		Determine the standard deviation of a population.
8		`STDDEV_SAMP`		Determine the standard deviation of a sample.
9		`VAR_POP`		Determine the variance of a population.
10		`VAR_SAMP`		Determine the variance of a sample.
11		`MEDIAN`		Determine the median of an ordered set of values.
12		`PERCENTILE_CONT`		Determine the percentile of an ordered set of values.
13		`PERCENTILE_DISC`		Determine the percentile of an ordered set of values.
14	Mathematical functions	Trigonometric functions	`SIN`	Return the sine (SIN trigonometric function) of the target data, which must be specified in radians.
15			`COS`	Return the cosine (COS trigonometric function) of the target data, which must be specified in radians.
16			`TAN`	Return the tangent (TAN trigonometric function) of the target data, which must be specified in radians.
17			`ASIN`	Return the angle (in radians) that is the inverse sine of the target data, in the range -/2 to /2.
18			`ACOS`	Return the angle (in radians) that is the inverse cosine of the target data, in the range 0 to .
19			`ATAN`	Return the angle (in radians) that is the inverse tangent of the target data, in the range -/2 to /2.
20			`ATAN2`	Return the angle (in radians) that is the inverse tangent of `y`/`x`, in the range - to .
21			`SINH`	Return the hyperbolic sine of the target data.
22			`COSH`	Return the hyperbolic cosine of the target data.
23			`TANH`	Return the hyperbolic tangent of the target data.
24			`DEGREES`	Return the result of converting an angle from radians to degrees.
25			`RADIANS`	Return the result of converting an angle from degrees to radians.
26			`PI`	Return the value of .
27		Exponent and logarithm	`POWER`	Return the result of raising the target data to a specified power.
28			`LOG`	Return the logarithm of the target data (antilogarithm) to the specified base.
29			`LN`	Return the natural logarithm of the target data.
30			`EXP`	Return the result of raising the base of the natural logarithm to a power.
31		Numerical calculation	`MOD`	Return the remainder after dividing the dividend by the divisor.
32			`ABS`	Return the absolute value of the target data.
33			`SQRT`	Return the square root of the target data.
34			`SIGN`	Return the sign of the target data (`+1` for positive, `-1` for negative, `0` for `0`).
35			`RANDOM`	Return pseudorandom numbers that follow a uniform distribution and are greater than or equal to the minimum specified value and less than the maximum specified value.
36			`RANDOMCURSOR`	Return pseudorandom numbers that follow a uniform distribution and are greater than or equal to the value specified for the minimum value and less than the value specified for the maximum value. If an SQL statement contains multiple `RANDOMCURSOR` functions for which the same identification number is specified, those functions always return the same values.
37			`RANDOMROW`	Return pseudorandom numbers that follow a uniform distribution and are greater than or equal to the value specified for the minimum value and less than the value specified for the maximum value. If a query specification contains multiple `RANDOMROW` functions for which the same identification number is specified, those functions return the same values for each result row of the query specification.
38			`RANDOM_NORMAL`	Return pseudorandom numbers that follow a normal distribution with an average μ and a standard deviation σ.
39		Rounding	`ROUND`	Return the value of the target data rounded to the `n`th digit after the decimal point.
40			`TRUNC`	Return a value that has been truncated to the specified number of decimal places.
41			`FLOOR`	Return the greatest integer that is equal to or less than the value of the target data.
42			`CEIL`	Return the smallest integer that is equal to or greater than the target data.
43	Character string functions	Character string retrieval	`CONTAINS`	Return whether character strings that meet the search condition expression are included in the target data.
44		Concatenating character string data	`CONCAT`	Concatenate two character string data items.
45		Extracting a substring from character string data	`SUBSTR`	Extract a substring from a character string starting from any position in the character string data.
46			`LEFT`	Extract a substring from a character string starting from the beginning (leftmost position) of the character string data.
47			`RIGHT`	Extract a substring from a character string starting from the end (rightmost position) of the character string data.
48		Removing characters from character string data	`TRIM`	Remove instances of the specified characters from the target character string. The characters can be removed in any of the following ways: Remove the specified characters starting from the beginning of the character string. Remove the specified characters starting from the end of the character string. Remove characters starting from both the beginning and the end of the character string.
49			`LTRIM`	Remove instances of the specified characters, starting from the beginning of the target character string.
50			`RTRIM`	Remove instances of the specified characters, starting from the end of the target character string.
51		Padding character strings	`LPAD`	Pad the beginning of the target data (from the left) with the padding character string up to the specified number of characters.
52		Padding character strings	`RPAD`	Pad the end of the target data (from the right) with the padding character string up to the specified number of characters.
53		Replacement of character strings in character string data	`REPLACE`	Replace any character string in the target data. All instances of the character string to be replaced in the target data are replaced with the replacement character string.
54		Replacement of characters in character string data	`TRANSLATE`	Replace any character in the target data.
55		Number of characters in character string data	`LENGTH`	Return the number of characters in the target character string.
56		Starting position of a character string in character string data	`INSTR`	Search the target data for a character string and return the starting position of the string.
57		Conversion between uppercase and lowercase letters	`LOWER`	Convert uppercase letters (`A` to `Z`) to lowercase letters (`a` to `z`) in character string data.
58		Conversion between uppercase and lowercase letters	`UPPER`	Convert lowercase letters (`a` to `z`) to uppercase letters (`A` to `Z`) in character string data.
59	Datetime functions	`DATEDIFF`		Return the difference between the start date and time and the end date and time.
60		`DAYOFWEEK`		Return the day of the week that the specified date falls on.
61		`DAYOFYEAR`		Return the specified date as the number of days elapsed since January 1 of that year.
62		`EXTRACT`		Extract a part (year, month, day, hour, minute, or second) from data representing the date and time.
63		`GETAGE`		Determine a person's age on a reference date given their birth date.
64		`LASTDAY`		Return the date or datetime of the last day of the month specified in the datetime data.
65		`ROUND`		Return the datetime data rounded to the unit specified in the datetime format.
66		`TRUNC`		Return the datetime data truncated to the unit specified in the datetime format.
67	Binary column functions	Concatenating binary data	`CONCAT`	Concatenate two binary data items.
68		Extracting a substring from binary data	`SUBSTRB`	Extract a substring from binary data starting from any position in the binary data.
69		Bit operations on binary data	`BITAND`	Return the bitwise logical AND of two binary data items.
70			`BITOR`	Return the bitwise inclusive OR of two binary data items.
71			`BITNOT`	Return the bitwise logical NOT of a binary data item.
72			`BITXOR`	Return the bitwise exclusive OR of two binary data items.
73			`BITLSHIFT`	Return the value resulting from shifting the bits of a binary data value to the left.
74			`BITRSHIFT`	Return the value resulting from shifting the bits of a binary data value to the right.
75	Data conversion functions	`CAST`		Convert the data type of the data.
76		`CONVERT`		Convert the data type of the data. In addition, by specifying a datetime format or number format, you can control the conversion as follows. By specifying a datetime format: When converting datetime data to character string data, you can specify the output format of the character string data after conversion. When converting character string data to datetime data, you can specify the input format of the character string data before conversion. By specifying a number format: When converting numeric data to character string data, you can specify the output format of the character string data after conversion. When converting character string data to numeric data, you can specify the input format of the character string data before conversion.
77		`ASCII`		Return the character code of the first character of the target data as an integer value.
78		`CHR`		Return the character corresponding to a character code represented by the integer target data.
79		`BIN`		Convert binary data to a binary string representation (character string data consisting of `0` and `1`).
80		`HEX`		Convert binary data to a hexadecimal string representation (character string data consisting of `0` to `9`, and `A` to `F`).
81	NULL evaluation functions	`COALESCE`		Evaluate the specified target data items in the order in which they are specified, and then return the first non-null value.
82		`ISNULL`
83		`NULLIF`		Compare target data 1 to target data 2 and return `NULL` if they are equal, or target data 1 if they are not equal.
84		`NVL`		Evaluate the specified target data items in the order in which they are specified, and then return the first non-null value.
85	Information acquisition functions	`LENGTHB`		Return the length of the target data in bytes.
86	Comparison functions	`DECODE`		Compare the values in the target data and the comparison data one at a time, and if there is a match, return the corresponding return value. If no match is found between the target data and comparison data, this function returns the predefined return value.
87		`LTDECODE`		Compare the values in the target data and in the comparison data one at a time, and, if any value in the target data is less than the value in the comparison data, return the corresponding return value. If no value in the target data is less than any of the values in the comparison data, this function returns the predefined return value.
88		`GREATEST`		Return the greatest value among the specified target data.
89		`LEAST`		Return the smallest value among specified target data.
90	Datetime information acquisition functions	`CURRENT_DATE`		Return the current date.
91		`CURRENT_TIME`		Return the current time.
92		`CURRENT_TIMESTAMP`		Return the current time stamp (date and time).
93	User information acquisition function	`CURRENT_USER`		Return the authorization identifier of the currently executing HADB user.

To Page Top