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

USING_NLS_COMP collation.

4

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.

See Also:

"SQL Operations and Their Derivation- and Determination-relevant Arguments"

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.

See Also:

"SQL Operations and Their Derivation- and Determination-relevant Arguments"

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

 
a1 || a2

Character Value Concatenation

 a1, a2

—

Operator

 
PRIOR a1

Hierarchical query parent value

 a1

—

Operator

 
CONNECT_BY_ROOT a1

Hierarchical query root value

 a1

—

Operator

 
SELECT a11, a21,...am1 
FROM ...

UNION ALL

SELECT a12, a22,...am2 
FROM ...

Non-distinct union of two row sets

 
a11, a12, a21, a22, ...am1, am2

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 ai2 (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 ai1 of the anchor member.

—

Operator

 
SELECT a11, a21,...am1 
FROM ... 

UNION

SELECT a12, a22,...am2 
FROM ...

Distinct union of two row sets

 
a11, a12, a21, a22, ...am1, am2

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.

 
a11, a12, a21, a22, ...am1, am2

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 a11, a21,...am1 
FROM ...

INTERSECT

SELECT a12, a22,...am2 
FROM ...

Distinct intersection of two row sets

 
a11, a12, a21, a22, ...am1, am2

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.

 
a11, a12, a21, a22, ...am1, am2

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 a11, a21,...am1 
FROM ...

MINUS

SELECT a12, a22,...am2 
FROM ...

Distinct subtraction of row sets

 
a11, a12, a21, a22, ...am1, am2

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.

 
a11, a12, a21, a22, ...am1, am2

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 c1 THEN r1 
  WHEN c2 THEN r2 
  ... 
  WHEN cn THEN rn 
ELSE 
  rn+1 
END

Searched case expression

 
r1,r2,...rn,rn+1

Each condition c1,...cn has independent collation determination.

Expression

 
CASE v 
  WHEN s1 THEN r1 
  WHEN s2 THEN r2 
  ... 
  WHEN sn THEN rn 
ELSE 
  rn+1 
END

Simple case expression; equivalent to:

CASE 
 WHEN v=s1 THEN r1
 WHEN v=s2 THEN r2
 ... 
 WHEN v=sn THEN rn 
ELSE 
  rn+1 
END

r1,r2,...rn,rn+1

v, s1, s2, ...sn
If collation of v does not dominate over collations of: 
s1, s2, ...sn
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

 
(a1,...an)

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

 
a1 =  a2
a1 <> a2
a1 <  a2
a1 >  a2
a1 >= a2
a1 <= a2

Simple comparison conditions

—

 
a1, a2

If a1 and a2 are lists, then see Expression list above.

Condition

 
a1 =  ANY (a2, ...an)
a1 <> ANY (a2, ...an)
a1 <  ANY (a2, ...an)
a1 >  ANY (a2, ...an)
a1 >= ANY (a2, ...an)
a1 <= ANY (a2, ...an)

(ANY may be replaced by SOME or ALL)

List comparison condition; equivalent to:

a1 <op> a2 AND|OR 
a1 <op> a3 AND|OR 
... 
a1 <op> an

—

 
a1, a2 
a1, a3 
...
a1, an

Collations are determined separately for each pair. If a1 to an are lists, then see Expression list above.

Condition

 
a1 = ANY 
(SELECT a2 FROM ...)

a1 <> ANY 
(SELECT a2 FROM ...)

a1 < ANY 
(SELECT a2 FROM ...)

a1 > ANY 
(SELECT a2 FROM ...)

a1 >= ANY 
(SELECT a2 FROM ...)

a1 <= ANY 
(SELECT a2 FROM ...)

(ANY may be replaced by SOME or ALL)

Query comparison conditions

—

 a1, a2

If a1 and a2 are lists, then see Expression list above.

Condition

 
a1 [NOT] LIKE [2|4|C] 
a2 ESCAPE a3

Check if pattern a2 matches string a1 using a3 as escape character in a2

—

 
a1, a2

Condition

 
REGEXP_LIKE(a1,a2,[a3])

Check if regular expression a2 matches string a1 according to flags in a3

—

 
a1, a2

Condition

 
a1 [NOT] BETWEEN 
a2 AND a3

Range comparison; equivalent to:

a1 >= a2 AND 
a1 <= a3

—

 
a1, a2 
a1, a3

Collation is determined separately for each comparison.

Condition

 
a1 [NOT] IN (a2,a3,...an)

Membership comparison; equivalent to:

a1 = ANY(a2,a3,...an)

—

 See =ANY above

Function

 
APPROX_COUNT_DISTINCT(a1)

Approximate count of distinct values of a1 in the result set

—

 a1

Function

 
ASCIISTR(a1)

Escape non-ASCII characters in a1 with Unicode escapes

 a1

—

Function

 
CAST(a1 AS <character data type>)

Cast value a1 to a character data type

a1, if a1 is of character data type; literal collation otherwise.

—

Function

 
CHR(a1)

Convert numeric code a1 to character and return as a VARCHAR2 string

Literal collation

—

Function

 
COALESCE(a1,a2,...an)

First non-null value among: a1, a2, ...an

COALESCE(a1,a2) is equivalent to:

CASE 
 WHEN a1 IS 
      NOT NULL 
 THEN a1 
ELSE a2 
END;

COALESCE(a1,a2,...an) is equivalent to:

CASE 
 WHEN a1 IS 
      NOT NULL 
THEN a1 
ELSE 
 COALESCE (a2,...an)
END;

a1, a2, ...an

—

Function

 
COLLATION(a1)

Return name of derived collation of a1 as string

Literal collation

—

Function

 
COLLECT( 
[DISTINCT] a1 
ORDER BY a2)

Aggregate into a nested table

—

a1 for DISTINCT

a2 for ORDER BY

Function

 
COMPOSE(a1)

Normalize a1 to Unicode NFC

 a1

—

Function

 
CONCAT(a1,a2)

Concatenate strings a1 and a2

 a1, a2

—

Function

 
CONVERT(a1[,a2[,a3]])

Convert character set of a1 from a3 to a2

 a1

—

Function

 
COUNT(DISTINCT a1)

Count distinct values of a1 in the result set

—

 a1

Function

 
CORR_K(a1,a2,a3)

Kendall's tau-b correlation coefficient

—

 
a1, a2

Collation is determined independently for each argument.

Function

 
CORR_S(a1,a2,a3)

Spearman's rho correlation coefficient

—

 
a1, a2

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([a1])

Current dimension value in a model clause

Collation of the dimension column to which CV() call corresponds, a1 or implicit

—

Function

 
DBTIMEZONE

Database time zone as string

Literal collation

—

Function

 
DECODE(v1,s1,r1,s2,r2,...,sn,rn,rn+1)

Value selection

 
r1,r2,...rn,rn+1

v1, s1, s2, ...sn

Function

 
DECOMPOSE(a1,a2)

Unicode normalization (NFD, NFKD); a2 is the requested normalization form

 a1

—

Function

 
DENSE_RANK([a1,a2,...an])

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(a1[,a2[,a3[,a4]]])

Debugging dump of a4 bytes of value a1 in format a2 from position a3

Literal collation

—

Function

 EMPTY_CLOB

Empty CLOB locator

 USING_NLS_COMP

—

Function

 
EXTRACT(
  TIMEZONE_REGION |
  TIMEZONE_ABBR 
FROM a1)

Extract time zone information from the datetime value a1

Literal collation

—

Function

 
EXTRACTVALUE(a1,a2[,a3])

Extract element value from XMLType

Literal collation

—

Function

 
FIRST_VALUE(a1)

First value of a1 from a set of rows

 a1

—

Function

 
GREATEST(a1,a2,...an)

Largest value among a1, ...an

 
a1, a2, ...an

a1, a2, ...an

Function

 
INITCAP(a1)

Capitalize initial letters of a1

 a1

—

Function

 
INSTR[B|2|4|C](a1,a2[,a3[,a4]])

Position of a4-th occurrence of string a2 in string a1 starting at position a3

—

 
a1, a2

Function

 
JSON_QUERY(a1,a2,...)

Retrieve fragment of the JSON object a1 described by the JSON path expression a2 as a string

Literal collation

—

Function

 
JSON_TABLE(a1,a2,...)

Present fragment of the JSON object a1 described by the JSON path expression a2 as a virtual relational table

Literal collation (for each character data type column in the generated table)

—

Function

 
JSON_VALUE(a1,a2,...)

Retrieve a scalar value from the JSON object a1 described by the JSON path expression a2 as an SQL scalar value

Literal collation

—

Function

 
LAG(a1[,a2[,a3]])

Value of a1 at row offset a2, or a3, if outside of window

 a1

—

Function

 
LAST_VALUE(a1)

Last value of a1 from a set of rows

 a1

—

Function

 
LEAD(a1[,a2[,a3]])

Value of a1 at row offset a2, or a3, if outside of window

 a1

—

Function

 
LEAST(a1,a2,...an)

Smallest value among a1, ...an

 
a1, a2, ...an

a1, a2,...an

Function

 
LISTAGG(a1[,a2])

Aggregate values of a1 from multiple rows into a list; a2 - separator

a1, if a1 is of character data type, otherwise literal collation if not RAW

—

Function

 
LOWER(a1)

Lowercase a1

 a1

—

Function

 
LPAD(a1,a2[,a3])

Pad string a1 with string a3 on the left up to display length a2

 a1

—

Function

 
LTRIM(a1[,a2])

Remove characters from the beginning of a1 as long as they can be found in string a2

 a1 a1

Function

 
MAX(a1)

Maximum value of a1 in the result set

 a1 
a1

Function

 
MIN(a1)

Minimum value of a1 in the result set

 a1 a1

Function

 
NCHR(a1)

Convert numeric code a1 to character and return as a NVARCHAR2 string

Literal collation

—

Function

 
NLS_CHARSET_NAME(a1)

Name of the character set with ID a1

Literal collation

—

Function

 
NLS_COLLATION_NAME(a1)

Name of the collation with ID a1

Literal collation

—

Function

 
NLS_INITCAP(a1[,a2])

Capitalize initial letters of a1 optionally using collation specified in a2

 a1

a1

Collation specified with NLS_SORT in a2 overrides collation of a1, but only at the execution time.

COLLATION(NLS_INITCAP(a1, a2)) returns collation of a1

Function

 
NLS_LOWER(a1[,a2])

Lowercase a1 optionally using collation specified in a2

 a1

a1

Collation specified with NLS_SORT in a2 overrides collation of a1, but only at the execution time.

COLLATION(NLS_LOWER(a1, a2)) returns collation of a1

Function

 
NLS_UPPER(a1[,a2])

Capitalize a1 optionally using collation specified in a2

 a1

a1

Collation specified with NLS_SORT in a2 overrides collation of a1, but only at the execution time.

COLLATION(NLS_UPPER(a1, a2)) returns collation of a1

Function

 
NLSSORT(a1[,a2])

Generate collation key for a1 optionally using collation specified in a2

—

a1

Collation specified with NLS_SORT in a2 overrides the collation of a1, but only at the execution time.

Function

 
NTH_VALUE(a1,n)

The n-th value of a1 from a set of rows

 a1

—

Function

 
NULLIF(a1,a2)

NULL, if a1=a2, otherwise a1

This is equivalent to:

CASE 
 WHEN a1=a2 
 THEN NULL 
ELSE a1
END;
a1 
a1, a2

Function

 
NVL(a1,a2)

a1, if a1 is not NULL, otherwise a2

 a1, a2

—

Function

 
NVL2(a1,a2,a3)

a2, if a1 is not NULL, otherwise a3.

 a2, a3

—

Function

 
ORA_INVOKING_USER

Invoking user name

Literal collation

—

Function

 
PATH(a1)

Path to a resource

Literal collation

—

Function

 
PERCENT_RANK([a1,a2,...an])...

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(a1,a2,a3)

If the cell reference a1 exists before execution of the enclosing model clause and is not null when the function is evaluated, then a2 else a3

 a2, a3

—

Function

 
PRESENTV(a1,a2,a3)

If the cell reference a1 exists before execution of the enclosing model clause, then a2, else a3.

 a2, a3

—

Function

 
PREVIOUS(a1)

Value of the cell reference a1 at the beginning of an iteration in a model clause

 a1

—

Function

 
RANK([a1,a2,...an])

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(a1)

Convert the RAW value a1 to its hexadecimal representation in a VARCHAR2 string

Literal collation

—

Function

 
RAWTONHEX(a1)

Convert the RAW value a1 to its hexadecimal representation in a NVARCHAR2 string

Literal collation

—

Function

 
REGEXP_COUNT(a1,a2[,
a3[,a4]])

Number of times regular expression a2 matches substrings of string a1 according to flags a4 starting matching at position a3

—

 
a1, a2

Function

 
REGEXP_INSTR(a1,a2[,
a3[,a4[,a5[,a6[,a7]]]]])

Minimal position in a1 at which regular expression a2 matches substring of string a1 for the a4-th time according to flags a6 starting matching at position a3; a5 and a7 control which position is actually returned

—

 
a1, a2

Function

 
REGEXP_REPLACE(a1,a2[,
a3[,a4[,a5[,a6]]]])

Replace with string a3 all matches or the a5-th match of regular expression a2 with a substring of string a1 according to flags a6 starting matching at position a4

 a1 
a1, a2

Function

 
REGEXP_SUBSTR(a1,a2[,
a3[,a4[,a5[,a6]]]])

Return the a4-th matching substring of regular expression a2 in string a1 according to flags a5 starting matching at position a3. if a6 is specified, it is the index of sub-expression to return in place of the whole matching substring.

 a1 
a1, a2

Function

 
REPLACE(a1,a2[,a3])

a1 with every occurrence of a2 replaced with a3

 a1 
a1, a2

Function

 
ROWIDTOCHAR(a1)

Convert the rowid a1 to a VARCHAR2 string

Literal collation

—

Function

 
ROWIDTONCHAR(a1)

Convert the rowid a1 to a NVARCHAR2 string

Literal collation

—

Function

 
RPAD(a1,a2[,a3])

Pad string a1 with string a3 on the right up to display length a2

 a1

—

Function

 
RTRIM(a1[,a2])

Remove characters from the end of a1 as long as they can be found in string a2

 a1 
a1

Function

 
SESSIONTIMEZONE

Database time zone as string

Literal collation

—

Function

 
SOUNDEX(a1)

Soundex representation of a1 (for phonetic comparison)

 a1

—

Function

 
STATS_BINOMIAL_TEST(a1,a2,a3[,a4])

Exact probability test of dichotomous variables a1 and a2

—

 
a1

Function

 
STATS_CROSSTAB(a1,a2[,
a3])

Crosstab analysis of a1 and a2

—

 
a1, a2

Collation is determined independently for each argument.

Function

 
STATS_F_TEST(a1,a2[,
a3[,a4]])

Variance analysis of a1 and a2

—

 
a1

Function

 
STATS_KS_TEST(a1,a2[,
a3])

Kolmogorov-Smirnov function

—

 
a1, a2

Collation is determined independently for each argument.

Function

 
STATS_MODE(a1)

Most frequent value of a1 in the result set

 a1 
a1

Function

 
STATS_MW_TEST(a1,a2[,
a3])

Mann Whitney test

—

 
a1, a2

Collation is determined independently for each argument.

Function

 
STATS_ONE_WAY_ANOVA(a1,a2[,a3])

One-way analysis of variance

—

 
a1

Function

 
STATS_T_TEST_INDEP(a1,a2[,a3[,a4]])

T-test of independent groups with same variance

—

 
a1

Function

 
STATS_T_TEST_INDEPU(a1,a2[,a3[,a4]])

T-test of independent groups with unequal variance

—

 
a1

Function

 
SUBSTR[B|2|4|C](a1,a2[,a3])

Substring of a1 starting at position a2 of length a3

 a1

—

Function

 
SYS_CONNECT_BY_PATH(a1,a2)

Path of value a1 from root to node, with column values separated by character a2

 a1

—

Function

 
SYS_CONTEXT(a1,a2[,a3])

Context parameter a2 of length a3 from namespace a1

Literal collation

—

Function

 
TO_CHAR(a1)

/*character*/

Convert a1 from data type CLOB, NCHAR, NVARCHAR2, or NCLOB to VARCHAR2

 a1

—

Function

 
TO_CHAR(a1[,a2[,a3]])

/*datetime*/

Convert a1 from a datetime data type to VARCHAR2 with optional format a2 and NLS environment a3

Literal collation

—

Function

 
TO_CHAR(a1[,a2[,a3]])

/*number*/

Convert a1 from a numeric data type to VARCHAR2 with optional format a2 and NLS environment a3

Literal collation

—

Function

 
TO_CLOB(a1)

Convert a1 from data type CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, or NCLOB to CLOB

a1

(must yield USING_NLS_COMP)

—

Function

 
TO_LOB(a1)

/*long*/

Convert a1 from data type LONG to CLOB

a1

(must yield USING_NLS_COMP)

—

Function

 
TO_MULTI_BYTE(a1)

Map normal-width characters in a1 to full-width characters

 a1

—

Function

 
TO_NCHAR(a1)

/*character*/

Convert a1 from data type NCLOB, CHAR, VARCHAR2, or CLOB to NVARCHAR2

 a1

—

Function

 
TO_NCHAR(a1[,a2[,a3]])

/*datetime*/

Convert a1 from a datetime data type to NVARCHAR2 with optional format a2 and NLS environment a3

Literal collation

—

Function

 
TO_NCHAR(a1[,a2[,a3]])

/*number*/

Convert a1 from a numeric data type to NVARCHAR2 with optional format a2 and NLS environment a3

Literal collation

—

Function

 
TO_NCLOB(a1)

Convert a1 from data type CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, or NCLOB to NCLOB

 a1

(must yield USING_NLS_COMP)

—

Function

 
TO_SINGLE_BYTE(a1)

Map full-width characters in a1 to normal-width characters

 a1

—

Function

 
TRANSLATE(a1,a2,a3)

Transform a1 by mapping characters in a2 to corresponding characters in a3

 a1 
a1

Function

 
TRANSLATE(a1 USING CHAR_CS|NCHAR_CS)

Convert a1 from one character set form to another

(roughly equivalent to: TO_CHAR| TO_NCHAR /*character*/)

 a1

—

Function

 
TRIM([[LEADING|TRAILING|BOTH] [a1] FROM] a2)

Remove all occurrences of character a1 at the beginning and/or at the end of a2

 a2 
a2

Function

 
TZ_OFFSET(a1)

Offset for the time zone a1

Literal collation

—

Function

 
UNISTR

Transform string a1 into an NVARCHAR2 string interpreting Unicode escapes

 a1

—

Function

 
UPPER(a1)

Capitalize string a1

 a1

—

Function

 
USER

Login user name

Literal collation

—

Function

 
USERENV(a1)

USERENV context parameter a1

Literal collation

—

Function

 
XMLCAST(a1 AS 
<data type>)

Cast result of XMLQuery to data type

Literal collation

—

Function

 
XMLSERIALIZE(... a1 
 [AS VARCHAR2 | CLOB]...)

Serialize XML document a1 to a string

Literal collation

—

Function

 
XMLTABLE(... COLUMNS 
col1 <data type> ...
coln <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 a1, a2, ...an)

Analytic clause partitioning

—

 
a1
a2
...
an

Collation is determined separately for each character argument in the clause.

Clause

 
OVER(ORDER BY a1, a2, ...an)

Analytic clause ordering

—

 
a1
a2
...
an

Collation is determined separately for each character argument in the clause.

Clause

 
ORDER BY a1, a2, ...an

Aggregate function ordering

—

 
a1
a2
...
an

Collation is determined separately for each character argument in the clause.

Clause

 
ORDER BY a1, a2, ...an

Query result ordering

—

 
a1
a2
...
an

Collation is determined separately for each character argument in the clause.

Clause

 
GROUP BY a1, a2, ...an

Query row grouping

—

 
a1
a2
...
an

Collation is determined separately for each character argument in the clause.