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.

Collation Derivation for Bind Variable References

In OCI, you can pass a collation for a bind variable in a query or a DML statement using the value of the OCI_ATTR_COLLATION_ID attribute. The OCI_ATTR_COLLATION_ID attribute can be set on a bind variable handle to any of the supported collation IDs using the OCIAttrSet() function. The IDs of both named collations and pseudo-collations are allowed. In this case, the derived coercibility level of the bind variable reference is 0.

When the OCI_ATTR_COLLATION_ID attribute value is set to OCI_COLLATION_NONE (the default value) on a bind variable handle, the collation of the bind variable is USING_NLS_COMP and the derived coercibility level of the bind variable reference is 4.

OCI does not check whether a collation is valid for a given data type of a bind variable. If the OCI_ATTR_COLLATION_ID attribute value is set for a non-character data type variable, it is ignored by the database server.

Collation of bind variables is currently ignored in PL/SQL expressions. For forward compatibility reasons, the OCI_ATTR_COLLATION_ID attribute should not be set for bind variables passed to an anonymous PL/SQL block, unless the variables are referenced exclusively in SQL statements.

See Also:

Oracle Call Interface Programmer's Guide for more information about the OCI_ATTR_COLLATION_ID attribute.

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

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.