Collation Derivation and Determination Rules for SQL Operations

C Collation Derivation and Determination Rules for SQL Operations

This appendix describes collation derivation and determination rules for SQL operations. This appendix contains the following topics:

C.1 Collation Derivation

The process of determining the collation of a character result of an SQL operation is called collation derivation. Such operation may be an operator, column reference, character literal, bind variable reference, function call, CASE expression, or a query clause.

Each character value in an SQL expression has a derived collation and a derived coercibility level.

The derived collation and coercibility level of the basic expressions is described in the following table.

Table C-1 Derived Collation and Derived Coercibility Level of Various Expression Types

Type of Expression	Derived Collation	Derived Coercibility Level
Result of the `COLLATE` operator	The named collation or the pseudo-collation specified in the `COLLATE` operator	0
Data container reference such as table, view, or materialized view column reference	The declared named collation or the pseudo-collation of the data container	2
Result of a PL/SQL function call or a user-defined operator	`USING_NLS_COMP` collation	2
Character literal	`USING_NLS_COMP` collation, if included in a top-level statement; else default collation of a view, a materialized view, or a PL/SQL unit, if included in its source	4
Character bind variable reference when the `OCI_ATTR_COLLATION_ID` attribute is not set on the corresponding bind variable handle	`USING_NLS_COMP` collation	4
Character bind variable reference when the `OCI_ATTR_COLLATION_ID` attribute is set on the corresponding bind variable handle	Collation with ID passed as the attribute value	0

Note:

Coercibility level 1 corresponds to no collation assigned
Coercibility level 3 is reserved for future use

The derived collation and coercibility level of an operation’s result is based on the collations and coercibility levels of the operation's arguments. A derivation-relevant character argument of an operation is an argument used to derive the collation of the operator’s result. An operator may have zero or more derivation-relevant character arguments, and zero or more other character arguments, such as flags or other control information not directly interacting with the derivation-relevant arguments. An argument is considered derivation-relevant, if its value is included in the result, either after some transformation or without undergoing any transformation.

An argument that is a format model, a pattern, a flag string, or a key into a virtual table of system information is not considered a derivation-relevant argument. For example, the built-in function TO_CHAR(arg1,arg2,arg3) has no derivation-relevant arguments, as the main argument arg1 is not of a character data type. The two character arguments arg2 and arg3 are not derivation-relevant arguments as they only define the format and parameters for the conversion of the main argument arg1.

The derived collation and coercibility level of the result of an operation without derivation-relevant arguments are the same as when a character literal would have been put in that expression in the place of the operation.

The following are the collation derivation rules for operations that return character values and have derivation-relevant arguments. These rules are applied recursively in an expression tree. These rules are based on the SQL standard version ISO/IEC 9075-2:1999.

The derived collation of a result of an operation with derivation-relevant character arguments arg1, arg2, …, argn is:

If at least one argument has the coercibility level 0, then all the arguments with coercibility level 0 must have the same collation, which is the derived collation of the result. The coercibility level of the result is 0. If two arguments with coercibility level 0 have different collations, then an error is reported.
Otherwise, if at least one argument has the coercibility level 1, then the expression result has the coercibility level 1 and no collation is assigned to it.
Otherwise, if LCL is the numerically lowest coercibility level of the arguments, then:
- If all the arguments with LCL have the same collation, then this collation is the derived collation of the result, and the coercibility level of the result is LCL.
- Otherwise, the result of the expression has the coercibility level 1 and no collation is assigned to it.

Note:

Set operators have arguments that are expression lists. For set operators, collation derivation is performed separately on corresponding elements of each of the arguments of the expression list. For example, in the query:

SELECT expr1, expr2 FROM t1
UNION
SELECT expr3, expr4 FROM t2

the collation is derived separately for the first and the second column of the result set. For the first column, the collation derivation rules are applied to expr1 and expr3. For the second column, the rules are applied to expr2 and expr4.

C.2 Collation Determination

Collation determination is the process of selecting the right collation to apply during the execution of a collation-sensitive operation. A collation-sensitive operation can be an SQL operator, condition, built-in function call, CASE expression or a query clause.

For Oracle Database releases earlier to 12.2, collation to be applied by an operation is determined by only the NLS_SORT and NLS_COMP session parameters.

Note:

The optional second parameters to NLS_UPPER, NLS_LOWER, NLS_INITCAP, and NLSSORT are exceptions.

Starting from Oracle Database 12.2, collation to be applied by an operation is determined by the derived data-bound collations of its arguments. Once a pseudo-collation is determined as the collation to use, NLS_SORT and NLS_COMP session parameters are checked to provide the actual named collation to apply.

Note:

The collation determination does not have to apply to the same operation to which collation derivation applies. For example, TO_CHAR function is not collation-sensitive, so it does not need collation determination. But, TO_CHAR function returns a character result that needs a collation declaration, hence collation derivation applies to it. Conversely, INSTR function needs to match characters and needs a collation determined for this match operation. However, the result of INSTR function is a number, hence no collation derivation is required for it.

The determination-relevant character argument of an operation is an argument used to determine the collation to be used by the operation. A collation-sensitive operation may have one or more determination-relevant character arguments and zero or more other character arguments, such as flags or other control information not directly interacting with the determination-relevant arguments.

An argument is considered determination-relevant, if its value is compared during the evaluation of an operation. An argument that is a format model, a flag string, or a key into a virtual table of system information is not considered a determination-relevant argument. However, a pattern argument can be a determination-relevant argument. For example, two of the three arguments of the LIKE predicate – argument and pattern – are determination-relevant arguments. The third argument – the escape character – is not considered determination-relevant argument. Another example is the built-in function REGEXP_COUNT, which has four arguments – source_char, pattern, position, and match_param. The determination-relevant arguments are source_char and pattern, which contain the strings to be compared. The non-determination-relevant character argument are position, which is numeric, and match_param, which provides parameters for the matching operation.

The following are the collation determination rules to determine the collation to use for an operation with determination-relevant character arguments arg1, arg2, …, argn. These rules are based on the SQL standard version ISO/IEC 9075-2:1999.

If operation is the equality condition and is used to enforce a foreign key constraint, then the collation to be used is the declared collation of the primary or unique key column being referenced. This declared collation must be the same as the declared collation of the foreign key column.
Otherwise, if at least one argument has the derived coercibility level 0, then all the arguments with coercibility level 0 must have the same collation, and this collation is used by the operation. If two arguments with coercibility level 0 have different collations, then an error is reported.
Otherwise, if at least one argument has the derived coercibility level 1, then an error is reported.
Otherwise, if LCL is the numerically lowest coercibility level of the arguments, then:
- If all arguments with LCL have the same collation, then that collation is used by the operation.
- Otherwise, an error is reported.

When the determined collation is a pseudo-collation, then the affected operation must refer to the session or database settings NLS_SORT or NLS_COMP or both to determine the actual named collation to apply. The database settings are used for expressions in virtual columns, CHECK constraints, and fine grained auditing (FGA) rules.

The collation determination rules for an operation involving a CLOB or an NCLOB data type value must result in the pseudo-collation USING_NLS_COMP, otherwise an error is reported.

Note:

Some conditions, set operators, and query clauses have arguments which are expression lists. In this case, collation determination is performed on the corresponding compared elements of each of the arguments in the expression list. For example, in the condition:

(expr1, expr2) IN (SELECT expr3, expr4 FROM t1)

the collation is determined separately for the pairs of compared elements. First, the collation determination rules are applied to expr1 and expr3. Then, the rules are applied to expr2 and expr4. When the condition is evaluated, values of expr1 are compared to values of expr3 using the first determined collation and values of expr2 are compared to values of expr4 using the second determined collation. Similarly, in the query:

SELECT expr1, expr2 FROM t1
MINUS
SELECT expr3, expr4 FROM t2

the collation determination rules are first applied to expr1 and expr3, then to expr2 and expr4. When the MINUS operator is evaluated, values of expr1 are compared to values of expr3 using the first determined collation and values of expr2 are compared to values of expr4 using the second determined collation.

In the query:

SELECT * FROM t1 ORDER BY expr1, expr2, expr3

rows are sorted first on values of expr1 using the derived collation expr1, then ties are broken by sorting on values of expr2 using the derived collation expr2, and then on values of expr3 using the derived collation expr3. Each position in the ORDER BY list is treated like a separate comparison operator for row values.

C.3 SQL Operations and Their Derivation- and Determination-relevant Arguments

The following table lists all the SQL operations that return a character value or are collation-sensitive or both. For each operation returning a character value, the table lists operation’s derivation-relevant arguments. If the operation has no such arguments, the fixed collation of the operation's result is shown instead. The term Literal Collation means that the collation derived for the operation's result is the collation of a character literal put in place of the operation in an expression; this is either USING_NLS_COMP for top-level SQL statements or the default collation of a view, materialized view, or a PL/SQL stored unit containing the expression in its source. For each collation-sensitive operation, the following table lists the operation’s determination-relevant arguments.

Table C-2 Derivation- and Determination-relevant Arguments for SQL Operations

Operation Type	Operation Name	Operation Description	Derivation-relevant Arguments or Fixed Collation	Determination-relevant Arguments
Pseudo-column	`VERSIONS_OPERATION`	Operation type in a flashback version query	Literal collation	—
Pseudo-column	`COLUMN_VALUE`	Value of nested table element of character data type	`USING_NLS_COMP`	—
Operator	`a₁ \|\| a₂`	Character Value Concatenation	`a₁, a₂`	—
Operator	`PRIOR a₁`	Hierarchical query parent value	`a₁`	—
Operator	`CONNECT_BY_ROOT a₁`	Hierarchical query root value	`a₁`	—
Operator	`SELECT a₁₁, a₂₁,...a_m1 FROM ... UNION ALL SELECT a₁₂, a₂₂,...a_m2 FROM ...`	Non-distinct union of two row sets	`a₁₁, a₁₂, a₂₁, a₂₂, ...a_m1, a_m2` Collation for each column of the resulting row set is derived separately by combining collations of columns from each of the two argument row sets. Special case: if an argument `a_i2` `(1<=i<=m)` belongs to a recursive member in a `WITH` clause and it is calculated recursively, then the collation is derived from the corresponding argument `a_i1` of the anchor member.	—
Operator	`SELECT a₁₁, a₂₁,...a_m1 FROM ... UNION SELECT a₁₂, a₂₂,...a_m2 FROM ...`	Distinct union of two row sets	`a₁₁, a₁₂, a₂₁, a₂₂, ...a_m1, a_m2` Collation for each column of the resulting row set is derived separately by combining collations of columns from each of the two argument row sets.	`a₁₁, a₁₂, a₂₁, a₂₂, ...a_m1, a_m2` Collation for comparison of each column of the argument row set is determined separately by combining collations of columns from each of the two argument row sets.
Operator	`SELECT a₁₁, a₂₁,...a_m1 FROM ... INTERSECT SELECT a₁₂, a₂₂,...a_m2 FROM ...`	Distinct intersection of two row sets	`a₁₁, a₁₂, a₂₁, a₂₂, ...a_m1, a_m2` Collation for each column of the resulting row set is derived separately by combining collations of columns from each of the two argument row sets.	`a₁₁, a₁₂, a₂₁, a₂₂, ...a_m1, a_m2` Collation for comparison of each column of the argument row set is determined separately by combining collations of columns from each of the two argument row sets.
Operator	`SELECT a₁₁, a₂₁,...a_m1 FROM ... MINUS SELECT a₁₂, a₂₂,...a_m2 FROM ...`	Distinct subtraction of row sets	`a₁₁, a₁₂, a₂₁, a₂₂, ...a_m1, a_m2` Collation for each column of the resulting row set is derived separately by combining collations of columns from each of the two argument row sets.	`a₁₁, a₁₂, a₂₁, a₂₂, ...a_m1, a_m2` Collation for comparison of each column of the argument row set is determined separately by combining collations of columns from each of the two argument row sets.
Expression	`CASE WHEN c₁ THEN r₁ WHEN c₂ THEN r₂ ... WHEN c_n THEN r_n ELSE r_n+1 END`	Searched case expression	`r₁,r₂,...r_n,r_n+1`	Each condition `c₁,...c_n` has independent collation determination.
Expression	`CASE v WHEN s₁ THEN r₁ WHEN s₂ THEN r₂ ... WHEN s_n THEN r_n ELSE r_n+1 END`	Simple case expression; equivalent to: `CASE WHEN v=s₁ THEN r₁ WHEN v=s₂ THEN r₂ ... WHEN v=s_n THEN r_n ELSE r_n+1 END`	`r₁,r₂,...r_n,r_n+1`	`v, s₁, s₂, ...s_n` If collation of `v` does not dominate over collations of: `s₁, s₂, ...s_n` then simple case is transformed to searched case internally.
Expression	Object Access Expression	Reference to an object method	`USING_NLS_COMP`	—
Expression	`:name`	Bind variable reference	Literal collation	—
Expression	`(a₁,...a_n)`	Expression list	Each list element has its collation derived separately and independently.	When two lists are compared, the collation determination is performed separately and independently for each of the two character data type elements at the same index in both the lists.
Condition	`a₁ = a₂ a₁ <> a₂ a₁ < a₂a₁ > a₂ a₁ >= a₂ a₁ <= a₂`	Simple comparison conditions	—	`a₁, a₂` If `a₁` and `a₂` are lists, then see Expression list above.
Condition	`a₁ = ANY (a₂, ...a_n) a₁ <> ANY (a_2, ...a_n) a₁ < ANY (a₂, ...a_n) a₁ > ANY (a₂, ...a_n) a₁ >= ANY (a₂, ...a_n) a₁ <= ANY (a₂, ...a_n)` (`ANY` may be replaced by `SOME` or `ALL`)	List comparison condition; equivalent to: `a₁ <op> a₂ AND\|OR a₁ <op> a₃ AND\|OR ... a₁ <op> a_n`	—	`a₁, a₂ a₁, a₃ ... a₁, a_n` Collations are determined separately for each pair. If `a₁` to `a_n` are lists, then see Expression list above.
Condition	`a₁ = ANY (SELECT a₂ FROM ...) a₁ <> ANY (SELECT a₂ FROM ...) a₁ < ANY (SELECT a₂ FROM ...) a₁ > ANY (SELECT a₂ FROM ...) a₁ >= ANY (SELECT a₂ FROM ...) a₁ <= ANY (SELECT a₂ FROM ...)` (`ANY` may be replaced by `SOME` or `ALL`)	Query comparison conditions	—	`a₁, a₂` If `a₁` and `a₂` are lists, then see Expression list above.
Condition	`a₁ [NOT] LIKE [2\|4\|C] a₂ ESCAPE a₃`	Check if pattern `a₂` matches string `a₁` using `a₃` as escape character in `a₂`	—	`a₁, a₂`
Condition	`REGEXP_LIKE(a₁,a₂,[a₃])`	Check if regular expression `a₂` matches string `a₁` according to flags in `a₃`	—	`a₁, a₂`
Condition	`a₁ [NOT] BETWEEN a₂ AND a₃`	Range comparison; equivalent to: `a₁ >= a₂ AND a₁ <= a₃`	—	`a₁, a₂ a₁, a₃` Collation is determined separately for each comparison.
Condition	`a₁ [NOT] IN (a₂,a₃,...a_n)`	Membership comparison; equivalent to: `a₁ = ANY(a₂,a₃,...a_n)`	—	See `=ANY` above
Function	`APPROX_COUNT_DISTINCT(a₁)`	Approximate count of distinct values of `a₁` in the result set	—	`a₁`
Function	`ASCIISTR(a₁)`	Escape non-ASCII characters in `a₁`with Unicode escapes	`a₁`	—
Function	`CAST(a₁ AS <character data type>)`	Cast value `a₁` to a character data type	`a₁`, if `a₁` is of character data type; literal collation otherwise.	—
Function	`CHR(a₁)`	Convert numeric code `a₁` to character and return as a `VARCHAR2` string	Literal collation	—
Function	`COALESCE(a₁,a₂,...a_n)`	First non-null value among: `a₁, a₂, ...a_n` `COALESCE(a₁,a₂)` is equivalent to: `CASE WHEN a₁ IS NOT NULL THEN a₁ ELSE a₂ END;` `COALESCE(a₁,a₂,...a_n)` is equivalent to: `CASE WHEN a₁ IS NOT NULL THEN a₁ ELSE COALESCE (a₂,...a_n) END;`	`a₁, a₂, ...a_n`	—
Function	`COLLATION(a₁)`	Return name of derived collation of `a₁` as string	Literal collation	—
Function	`COLLECT( [DISTINCT] a₁ ORDER BY a₂)`	Aggregate into a nested table	—	`a₁` for `DISTINCT` `a₂` for `ORDER BY`
Function	`COMPOSE(a₁)`	Normalize `a₁` to Unicode NFC	`a₁`	—
Function	`CONCAT(a₁,a₂)`	Concatenate strings `a₁` and `a₂`	`a1, a2`	—
Function	`CONVERT(a₁[,a₂[,a₃]])`	Convert character set of `a₁` from `a₃` to `a₂`	`a₁`	—
Function	`COUNT(DISTINCT a₁)`	Count distinct values of `a₁` in the result set	—	`a₁`
Function	`CORR_K(a₁,a₂,a₃)`	Kendall's tau-b correlation coefficient	—	`a₁, a₂` Collation is determined independently for each argument.
Function	`CORR_S(a₁,a₂,a₃)`	Spearman's rho correlation coefficient	—	`a₁, a₂` Collation is determined independently for each argument.
Function	`CUBE_TABLE(...)`	OLAP cube or hierarchy to relational table	Literal collation (for each character data type column in the generated table)	—
Function	`CV([a₁])`	Current dimension value in a model clause	Collation of the dimension column to which CV() call corresponds, `a₁` or implicit	—
Function	`DBTIMEZONE`	Database time zone as string	Literal collation	—
Function	`DECODE(v₁,s₁,r₁,s₂,r₂,...,s_n,r_n,r_n+1)`	Value selection	`r₁,r₂,...r_n,r_n+1`	`v₁, s₁, s₂, ...s_n`
Function	`DECOMPOSE(a₁,a₂)`	Unicode normalization (NFD, NFKD); `a₂` is the requested normalization form	`a1`	—
Function	`DENSE_RANK([a₁,a₂,...a_n])`	Dense rank of a value in a group of values	—	Ranking is based on collation of the elements in function’s `ORDER BY` clause.
Function	`DUMP(a₁[,a₂[,a₃[,a₄]]])`	Debugging dump of `a₄` bytes of value `a₁` in format `a₂` from position `a₃`	Literal collation	—
Function	`EMPTY_CLOB`	Empty `CLOB` locator	`USING_NLS_COMP`	—
Function	`EXTRACT( TIMEZONE_REGION \| TIMEZONE_ABBR FROM a₁)`	Extract time zone information from the datetime value `a₁`	Literal collation	—
Function	`EXTRACTVALUE(a₁,a₂[,a₃])`	Extract element value from `XMLType`	Literal collation	—
Function	`FIRST_VALUE(a₁)`	First value of `a₁` from a set of rows	`a₁`	—
Function	`GREATEST(a₁,a₂,...a_n)`	Largest value among `a₁, ...a_n`	`a₁, a₂, ...a_n`	`a₁, a₂, ...a_n`
Function	`INITCAP(a₁)`	Capitalize initial letters of `a₁`	`a₁`	—
Function	`INSTR[B\|2\|4\|C](a₁,a₂[,a₃[,a₄]])`	Position of `a₄`-th occurrence of string `a₂` in string `a₁` starting at position `a₃`	—	`a₁, a₂`
Function	`JSON_QUERY(a₁,a₂,...)`	Retrieve fragment of the JSON object `a₁` described by the JSON path expression `a₂` as a string	Literal collation	—
Function	`JSON_TABLE(a₁,a₂,...)`	Present fragment of the JSON object `a₁` described by the JSON path expression `a₂` as a virtual relational table	Literal collation (for each character data type column in the generated table)	—
Function	`JSON_VALUE(a₁,a₂,...)`	Retrieve a scalar value from the JSON object `a₁` described by the JSON path expression `a₂` as an SQL scalar value	Literal collation	—
Function	`LAG(a₁[,a₂[,a₃]])`	Value of `a₁` at row offset `a₂`, or `a₃`, if outside of window	`a₁`	—
Function	`LAST_VALUE(a₁)`	Last value of `a₁` from a set of rows	`a₁`	—
Function	`LEAD(a₁[,a₂[,a₃]])`	Value of `a₁` at row offset `a₂`, or `a₃`, if outside of window	`a₁`	—
Function	`LEAST(a₁,a₂,...a_n)`	Smallest value among `a₁, ...a_n`	`a₁, a₂, ...a_n`	`a₁, a₂,...a_n`
Function	`LISTAGG(a₁[,a₂])`	Aggregate values of `a₁` from multiple rows into a list; `a₂` - separator	`a₁`, if `a₁` is of character data type, otherwise literal collation if not `RAW`	—
Function	`LOWER(a₁)`	Lowercase `a₁`	`a₁`	—
Function	`LPAD(a₁,a₂[,a₃])`	Pad string `a₁` with string `a₃` on the left up to display length `a₂`	`a₁`	—
Function	`LTRIM(a₁[,a₂])`	Remove characters from the beginning of `a₁` as long as they can be found in string `a₂`	`a₁`	`a₁`
Function	`MAX(a₁)`	Maximum value of `a₁` in the result set	`a₁`	`a₁`
Function	`MIN(a₁)`	Minimum value of `a₁` in the result set	`a₁`	`a₁`
Function	`NCHR(a₁)`	Convert numeric code `a₁` to character and return as a `NVARCHAR2` string	Literal collation	—
Function	`NLS_CHARSET_NAME(a₁)`	Name of the character set with ID `a₁`	Literal collation	—
Function	`NLS_COLLATION_NAME(a₁)`	Name of the collation with ID `a₁`	Literal collation	—
Function	`NLS_INITCAP(a₁[,a₂])`	Capitalize initial letters of `a₁` optionally using collation specified in `a₂`	`a₁`	`a₁` Collation specified with `NLS_SORT` in `a₂` overrides collation of `a₁`, but only at the execution time. `COLLATION(NLS_INITCAP(a1, a2))` returns collation of `a₁`
Function	`NLS_LOWER(a₁[,a₂])`	Lowercase `a₁` optionally using collation specified in `a₂`	`a₁`	`a₁` Collation specified with `NLS_SORT` in `a₂` overrides collation of `a₁`, but only at the execution time. `COLLATION(NLS_LOWER(a1, a2))` returns collation of `a₁`
Function	`NLS_UPPER(a₁[,a₂])`	Capitalize `a₁` optionally using collation specified in `a₂`	`a₁`	`a₁` Collation specified with `NLS_SORT` in `a₂` overrides collation of `a₁`, but only at the execution time. `COLLATION(NLS_UPPER(a1, a2))` returns collation of `a₁`
Function	`NLSSORT(a₁[,a₂])`	Generate collation key for `a₁` optionally using collation specified in `a₂`	—	`a₁` Collation specified with `NLS_SORT` in `a₂` overrides the collation of `a₁`, but only at the execution time.
Function	`NTH_VALUE(a₁,n)`	The n-th value of `a₁` from a set of rows	`a₁`	—
Function	`NULLIF(a₁,a₂)`	`NULL`, if `a₁=a₂`, otherwise `a₁` This is equivalent to: `CASE WHEN a₁=a₂ THEN NULL ELSE a₁ END;`	`a₁`	`a₁, a₂`
Function	`NVL(a₁,a₂)`	`a₁`, if `a₁` is not `NULL`, otherwise `a₂`	`a₁, a₂`	—
Function	`NVL2(a₁,a₂,a₃)`	`a₂`, if `a₁` is not `NULL`, otherwise `a₃`.	`a₂, a₃`	—
Function	`ORA_INVOKING_USER`	Invoking user name	Literal collation	—
Function	`PATH(a₁)`	Path to a resource	Literal collation	—
Function	`PERCENT_RANK([a₁,a₂,...a_n])...`	Percent rank of a value in a group of values	—	Ranking is based on collation of the elements in function’s `ORDER BY` clause.
Function	`PREDICTION`	Data mining prediction	Literal collation	—
Function	`PRESENTNNV(a₁,a₂,a₃)`	If the cell reference `a₁` exists before execution of the enclosing model clause and is not null when the function is evaluated, then `a₂` else `a₃`	`a₂, a₃`	—
Function	`PRESENTV(a₁,a₂,a₃)`	If the cell reference `a₁` exists before execution of the enclosing model clause, then `a₂`, else `a₃`.	`a₂, a₃`	—
Function	`PREVIOUS(a₁)`	Value of the cell reference a1 at the beginning of an iteration in a model clause	`a₁`	—
Function	`RANK([a₁,a₂,...a_n])`	Rank of a value in a group of values	—	Ranking is based on collation of the elements in function’s `ORDER BY` clause.
Function	`RAWTOHEX(a₁)`	Convert the RAW value `a₁` to its hexadecimal representation in a `VARCHAR2` string	Literal collation	—
Function	`RAWTONHEX(a₁)`	Convert the `RAW` value `a₁` to its hexadecimal representation in a `NVARCHAR2` string	Literal collation	—
Function	`REGEXP_COUNT(a₁,a₂[, a₃[,a₄]])`	Number of times regular expression `a₂` matches substrings of string `a₁` according to flags `a₄` starting matching at position `a₃`	—	`a₁, a₂`
Function	`REGEXP_INSTR(a₁,a₂[, a₃[,a₄[,a₅[,a₆[,a₇]]]]])`	Minimal position in `a₁` at which regular expression `a₂` matches substring of string `a₁` for the `a₄`-th time according to flags `a₆` starting matching at position `a₃`; `a₅` and `a₇` control which position is actually returned	—	`a₁, a₂`
Function	`REGEXP_REPLACE(a₁,a₂[, a₃[,a₄[,a₅[,a₆]]]])`	Replace with string `a₃` all matches or the `a₅`-th match of regular expression `a₂` with a substring of string `a₁` according to flags `a₆` starting matching at position `a₄`	`a₁`	`a₁, a₂`
Function	`REGEXP_SUBSTR(a₁,a₂[, a₃[,a₄[,a₅[,a₆]]]])`	Return the `a₄`-th matching substring of regular expression `a₂` in string `a₁` according to flags `a₅` starting matching at position `a₃`. if `a₆` is specified, it is the index of sub-expression to return in place of the whole matching substring.	`a₁`	`a₁, a₂`
Function	`REPLACE(a₁,a₂[,a₃])`	`a₁` with every occurrence of `a₂` replaced with `a₃`	`a₁`	`a₁, a₂`
Function	`ROWIDTOCHAR(a₁)`	Convert the rowid `a₁` to a `VARCHAR2` string	Literal collation	—
Function	`ROWIDTONCHAR(a₁)`	Convert the rowid `a₁` to a `NVARCHAR2` string	Literal collation	—
Function	`RPAD(a₁,a₂[,a₃])`	Pad string `a₁` with string `a₃` on the right up to display length `a₂`	`a₁`	—
Function	`RTRIM(a₁[,a₂])`	Remove characters from the end of `a₁` as long as they can be found in string `a₂`	`a₁`	`a₁`
Function	`SESSIONTIMEZONE`	Database time zone as string	Literal collation	—
Function	`SOUNDEX(a₁)`	Soundex representation of `a₁` (for phonetic comparison)	`a₁`	—
Function	`STATS_BINOMIAL_TEST(a₁,a₂,a₃[,a₄])`	Exact probability test of dichotomous variables `a₁` and `a₂`	—	`a₁`
Function	`STATS_CROSSTAB(a₁,a₂[, a₃])`	Crosstab analysis of `a₁` and `a₂`	—	`a₁, a₂` Collation is determined independently for each argument.
Function	`STATS_F_TEST(a₁,a₂[, a₃[,a₄]])`	Variance analysis of `a₁` and `a₂`	—	`a₁`
Function	`STATS_KS_TEST(a₁,a₂[, a₃])`	Kolmogorov-Smirnov function	—	`a₁, a₂` Collation is determined independently for each argument.
Function	`STATS_MODE(a₁)`	Most frequent value of `a₁` in the result set	`a₁`	`a₁`
Function	`STATS_MW_TEST(a₁,a₂[, a₃])`	Mann Whitney test	—	`a₁, a₂` Collation is determined independently for each argument.
Function	`STATS_ONE_WAY_ANOVA(a₁,a₂[,a₃])`	One-way analysis of variance	—	`a₁`
Function	`STATS_T_TEST_INDEP(a₁,a₂[,a₃[,a₄]])`	T-test of independent groups with same variance	—	`a₁`
Function	`STATS_T_TEST_INDEPU(a₁,a₂[,a₃[,a₄]])`	T-test of independent groups with unequal variance	—	`a₁`
Function	`SUBSTR[B\|2\|4\|C](a₁,a₂[,a₃])`	Substring of `a₁` starting at position `a₂` of length `a₃`	`a₁`	—
Function	`SYS_CONNECT_BY_PATH(a₁,a₂)`	Path of value `a₁` from root to node, with column values separated by character `a₂`	`a₁`	—
Function	`SYS_CONTEXT(a₁,a₂[,a₃])`	Context parameter `a₂` of length `a₃` from namespace `a₁`	Literal collation	—
Function	`TO_CHAR(a₁)` /character/	Convert `a₁` from data type `CLOB, NCHAR, NVARCHAR2,` or `NCLOB` to `VARCHAR2`	`a₁`	—
Function	`TO_CHAR(a₁[,a₂[,a₃]])` /datetime/	Convert `a₁` from a datetime data type to `VARCHAR2` with optional format `a₂` and NLS environment `a₃`	Literal collation	—
Function	`TO_CHAR(a₁[,a₂[,a₃]])` /number/	Convert `a₁` from a numeric data type to `VARCHAR2` with optional format `a₂` and NLS environment `a₃`	Literal collation	—
Function	`TO_CLOB(a₁)`	Convert `a₁` from data type `CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2`, or `NCLOB` to `CLOB`	`a₁` (must yield `USING_NLS_COMP`)	—
Function	`TO_LOB(a₁)` /long/	Convert `a₁` from data type `LONG` to `CLOB`	`a₁` (must yield `USING_NLS_COMP`)	—
Function	`TO_MULTI_BYTE(a₁)`	Map normal-width characters in `a₁` to full-width characters	`a₁`	—
Function	`TO_NCHAR(a₁)` /character/	Convert `a₁` from data type `NCLOB, CHAR, VARCHAR2,` or `CLOB` to `NVARCHAR2`	`a₁`	—
Function	`TO_NCHAR(a₁[,a₂[,a₃]])` /datetime/	Convert `a₁` from a datetime data type to `NVARCHAR2` with optional format `a₂` and NLS environment `a₃`	Literal collation	—
Function	`TO_NCHAR(a₁[,a₂[,a₃]])` /number/	Convert `a₁` from a numeric data type to `NVARCHAR2` with optional format `a₂` and NLS environment `a₃`	Literal collation	—
Function	`TO_NCLOB(a₁)`	Convert `a₁` from data type `CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2,` or `NCLOB` to `NCLOB`	`a₁` (must yield `USING_NLS_COMP`)	—
Function	`TO_SINGLE_BYTE(a₁)`	Map full-width characters in `a₁` to normal-width characters	`a₁`	—
Function	`TRANSLATE(a₁,a₂,a₃)`	Transform `a₁` by mapping characters in `a₂` to corresponding characters in `a₃`	`a1`	`a₁`
Function	`TRANSLATE(a₁ USING CHAR_CS\|NCHAR_CS)`	Convert `a₁` from one character set form to another (roughly equivalent to: `TO_CHAR\| TO_NCHAR` /character/)	`a₁`	—
Function	`TRIM([[LEADING\|TRAILING\|BOTH] [a₁] FROM] a₂)`	Remove all occurrences of character `a₁` at the beginning and/or at the end of `a₂`	`a₂`	`a₂`
Function	`TZ_OFFSET(a₁)`	Offset for the time zone `a₁`	Literal collation	—
Function	`UNISTR`	Transform string `a₁` into an `NVARCHAR2` string interpreting Unicode escapes	`a₁`	—
Function	`UPPER(a₁)`	Capitalize string `a₁`	`a₁`	—
Function	`USER`	Login user name	Literal collation	—
Function	`USERENV(a₁)`	`USERENV` context parameter `a₁`	Literal collation	—
Function	`XMLCAST(a₁ AS <data type>)`	Cast result of `XMLQuery` to data type	Literal collation	—
Function	`XMLSERIALIZE(... a₁ [AS VARCHAR2 \| CLOB]...)`	Serialize XML document `a₁` to a string	Literal collation	—
Function	`XMLTABLE(... COLUMNS col₁ <data type> ... col_n <data type>...)`	Present content of an XML object as a virtual relational table	Literal collation (for each character data type column in the generated table)	—
Clause	`OVER(PARTITION BY a₁, a₂, ...a_n)`	Analytic clause partitioning	—	`a₁ a₂ ... a_n` Collation is determined separately for each character argument in the clause.
Clause	`OVER(ORDER BY a₁, a₂, ...a_n)`	Analytic clause ordering	—	`a₁ a₂ ... a_n` Collation is determined separately for each character argument in the clause.
Clause	`ORDER BY a₁, a₂, ...a_n`	Aggregate function ordering	—	`a₁ a₂ ... a_n` Collation is determined separately for each character argument in the clause.
Clause	`ORDER BY a₁, a₂, ...a_n`	Query result ordering	—	`a₁ a₂ ... a_n` Collation is determined separately for each character argument in the clause.
Clause	`GROUP BY a₁, a₂, ...a_n`	Query row grouping	—	`a₁ a₂ ... a_n` Collation is determined separately for each character argument in the clause.