8.1 List of scalar functions

The following table lists all of the scalar functions.

Table 8‒1: List of scalar functions
No.	Category		Name of scalar function	Description
1	Mathematical functions	Trigonometric functions	`ACOS`	Returns the angle (in radians) that is the inverse cosine of the target data, in the range 0 to .
2			`ASIN`	Returns the angle (in radians) that is the inverse sine of the target data, in the range -/2 to /2.
3			`ATAN`	Returns the angle (in radians) that is the inverse tangent of the target data, in the range -/2 to /2.
4			`ATAN2`	Returns the angle (in radians) that is the inverse tangent of `y`/`x`, in the range - to .
5			`COS`	Returns the cosine (COS trigonometric function) of the target data, which must be specified in radians.
6			`COSH`	Returns the hyperbolic cosine of the target data.
7			`DEGREES`	Returns the result of converting the specified angle from radians to degrees.
8			`PI`	Returns the value of .
9			`RADIANS`	Returns the result of converting the specified angle from degrees to radians.
10			`SIN`	Returns the sine (SIN trigonometric function) of the target data, which must be specified in radians.
11			`SINH`	Returns the hyperbolic sine of the target data.
12			`TAN`	Returns the tangent (TAN trigonometric function) of the target data, which must be specified in radians.
13			`TANH`	Returns the hyperbolic tangent of the target data.
14		Exponent and logarithm calculations	`EXP`	Returns the result of raising the base of the natural logarithm to a power.
15			`LN`	Returns the natural logarithm of the target data.
16			`LOG`	Returns the logarithm of the target data (antilogarithm) to the specified base.
17			`POWER`	Returns the result of raising the target data to a specified power.
18		Numerical calculations	`ABS`	Returns the absolute value of the target data.
19			`CEIL`	Returns the smallest integer that is equal to or greater than the target data.
20			`FLOOR`	Returns the greatest integer that is equal to or less than the value of the target data.
21			`MOD`	Returns the remainder after dividing the dividend by the divisor.
22			`RANDOM`	Returns 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.
23			`RANDOMCURSOR`	Returns 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.
24			`RANDOMROW`	Returns 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.
25			`RANDOM_NORMAL`	Returns pseudorandom numbers that follow a normal distribution with an average μ and a standard deviation σ.
26			`ROUND`	Returns the value of the target data rounded to the `n`th digit after the decimal point.
27			`SIGN`	Returns the sign of the target data (`+1` for positive, `-1` for negative, `0` for `0`).
28			`SQRT`	Returns the square root of the target data.
29			`TRUNC`	Returns a value that has been truncated to the specified number of decimal places.
30	Character string functions	Character string operations	`CONCAT`	Concatenates two character string data items.
31			`LEFT`	Extracts a substring from a character string starting from the beginning (leftmost position) of the character string data.
32			`LPAD`	Pads the beginning (left side) of the target data with the padding character string up to the specified number of characters.
33			`LTRIM`	Removes instances of the specified characters, starting from the beginning of the target character string.
34			`RIGHT`	Extracts a substring from a character string starting from the end (rightmost position) of the character string data.
35			`RPAD`	Pads the end (right side) of the target data with the padding character string up to the specified number of characters.
36			`RTRIM`	Removes instances of the specified characters, starting from the end of the target character string.
37			`SUBSTR`	Extracts a substring from a character string starting from any position in the character string data.
38			`TRIM`	Removes 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.
39		Acquisition of character string information	`CONTAINS`	Returns whether character strings that meet the search condition expression are included in the target data.
40			`INSTR`	Searches the target data for a character string and returns the starting position of the string.
41			`LENGTH`	Returns the number of characters in the target character string.
42		Character substitution	`REPLACE`	Replaces any character string in the target data. All instances of the character string to be replaced in the target data are replaced with a replacement character string.
43		Character substitution	`TRANSLATE`	Replaces any character in the target data.
44		Character string conversion	`LOWER`	Converts uppercase letters (`A` to `Z`) to lowercase letters (`a` to `z`) in character string data.
45		Character string conversion	`UPPER`	Converts lowercase letters (`a` to `z`) to uppercase letters (`A` to `Z`) in character string data.
46	Datetime functions		`DATEDIFF`	Returns the difference between the start date and time and the end date and time.
47			`DAYOFWEEK`	Returns the day of the week that the specified date falls on.
48			`DAYOFYEAR`	Returns the specified date as the number of days elapsed since January 1 of that year.
49			`EXTRACT`	Extracts a part (year, month, day, hour, minute, or second) from data representing the date and time.
50			`GETAGE`	Determines a person's age on a reference date given their birth date.
51			`LASTDAY`	Returns the date or datetime of the last day of the month specified in the datetime data.
52			`ROUND`	Returns the datetime data rounded to the unit specified in the datetime format.
53			`TRUNC`	Returns the datetime data truncated to the unit specified in the datetime format.
54	Binary column functions	Binary data operation	`CONCAT`	Concatenates two binary data items.
55		Binary data operation	`SUBSTRB`	Extracts a substring from binary data starting from any position in the binary data.
56		Bit operations	`BITAND`	Returns the bitwise logical AND of two binary data items.
57			`BITLSHIFT`	Returns the value resulting from shifting the bits of a binary data value to the left.
58			`BITNOT`	Returns the bitwise logical NOT of a binary data item.
59			`BITOR`	Returns the bitwise inclusive OR of two binary data items.
60			`BITRSHIFT`	Returns the value resulting from shifting the bits of a binary data value to the right.
61			`BITXOR`	Returns the bitwise exclusive OR of two binary data items.
62	Data conversion functions		`ASCII`	Returns the character code of the first character of the target data as an integer value.
63			`BIN`	Converts binary data to a binary string representation (character string data consisting of `0` and `1`).
64			`CAST`	Converts the data type of the data.
65			`CHR`	Returns the character corresponding to the character code represented by the integer target data.
66			`CONVERT`	Converts the data type of the data. You can also specify a datetime format or number format to control the conversion. If you specify 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 pre-conversion input format of the character string data. If you specify 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.
67			`HEX`	Converts binary data to a hexadecimal string representation (character string data consisting of `0` to `9`, and `A` to `F`).
68	NULL evaluation functions		`COALESCE`	Evaluates the specified target data items in the order in which they are specified, and then returns the first non-null value.
69			`ISNULL`
70			`NULLIF`	Compares target data 1 to target data 2 and returns `NULL` if they are equal, or target data 1 if they are not equal.
71			`NVL`	Evaluates the specified target data items in the order in which they are specified, and then returns the first non-null value.
72	Information acquisition functions		`LENGTHB`	Returns the length of the target data in bytes.
73	Comparison functions		`DECODE`	Compares the values in the target data and the comparison data one at a time, and if there is a match, returns the corresponding return value. If no match is found between the target data and comparison data, this function returns the predefined return value.
74			`GREATEST`	Returns the greatest value among the specified target data items.
75			`LEAST`	Returns the smallest value among the specified target data items.
76			`LTDECODE`	Compares 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, returns 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.

To Page Top