Oracle NLS_COLLATION_ID() Function

Oracle NLS_COLLATION_ID() is a built-in function that returns the corresponding collation ID number based on the given collation name.

Oracle `NLS_COLLATION_ID()` Syntax

Here’s the syntax of the Oracle NLS_COLLATION_ID() function:

NLS_COLLATION_ID(collation_name)

collation_name: Required. Specify the collation name as a VARCHAR2 value for which you want to retrieve the collation ID. You can specify any combination of upper- and lowercase letters for valid named or pseudo collations.

The Oracle NLS_COLLATION_ID() function returns the corresponding collation ID number based on the given collation name.

This function returns a NUMBER value. If an invalid collation name is specified, the function returns NULL.

If any of the parameters are NULL, NLS_COLLATION_ID() returns NULL.

Here are a few examples that demonstrate the usage of the Oracle NLS_COLLATION_ID() function.

The following statement returns the ID for the BINARY_CI collation:

SELECT
    NLS_COLLATION_ID('BINARY_CI')
FROM dual;

Output:

   NLS_COLLATION_ID('BINARY_CI')
________________________________
                          147455

The following statement returns the ID for the BINARY collation:

SELECT
    NLS_COLLATION_ID('BINARY')
FROM dual;

Output:

   NLS_COLLATION_ID('BINARY')
_____________________________
                        16383

If any of the parameters are NULL, NLS_COLLATION_ID() returns NULL.

SET NULL 'NULL';
SELECT
    NLS_COLLATION_ID(NULL)
FROM dual;

Output:

   NLS_COLLATION_ID(NULL)
_________________________
                     NULL

In this example, we use the statement SET NULL 'NULL'; to display the NULL value as the string 'NULL'.

Oracle NLS_COLLATION_ID() is a built-in function that returns the corresponding collation ID number based on the given collation name.