Oracle Character Set Functions

Every Oracle database stores text in a character set, usually AL32UTF8 for VARCHAR2 and AL16UTF16 for NVARCHAR2. Oracle identifies character sets by name and by number, and some views and functions report only the number. Three functions convert between the two and tell you how many characters fit in an NCHAR column: NLS_CHARSET_ID, NLS_CHARSET_NAME, and NLS_CHARSET_DECL_LEN.

Code for This Guide

The main example is in the examples/character-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

nls_charset_id(string)
nls_charset_name(number)
nls_charset_decl_len(byte_count, char_set_id)

Convert Between Names and Numbers

Example:

select nls_charset_id('AL32UTF8')           as utf8_id,
       nls_charset_name(873)                as name_873,
       nls_charset_name(2000)               as name_2000,
       nls_charset_decl_len(200, nls_charset_id('AL16UTF16')) as nchar_chars
from   dual;

Output:

   UTF8_ID NAME_873    NAME_2000       NCHAR_CHARS
__________ ___________ ____________ ______________
       873 AL32UTF8    AL16UTF16               100

AL32UTF8 is number 873 and AL16UTF16 number 2000. A 200-byte NCHAR declaration in AL16UTF16, which uses two bytes per character, holds 100 characters.

Find the Database's Character Sets

The view NLS_DATABASE_PARAMETERS reports the database character set and the national character set by name; NLS_CHARSET_ID adds their numbers.

Example:

select parameter, value, nls_charset_id(value) as charset_id
from   nls_database_parameters
where  parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

Output:

PARAMETER                 VALUE           CHARSET_ID
_________________________ ____________ _____________
NLS_NCHAR_CHARACTERSET    AL16UTF16             2000
NLS_CHARACTERSET          AL32UTF8               873

NLS_CHARACTERSET applies to CHAR, VARCHAR2, and CLOB columns; NLS_NCHAR_CHARACTERSET to NCHAR, NVARCHAR2, and NCLOB.

Things to Know

• Some routines take a character set number rather than a name, such as the bfile_csid argument of DBMS_LOB.LOADCLOBFROMFILE; NLS_CHARSET_ID supplies it.
• Name lookups are case-insensitive: nls_charset_id('al32utf8') also returns 873.
• To convert text from one character set to another, use CONVERT or the UTL_RAW and UTL_I18N packages; these functions only identify character sets.

Related Guides

• Oracle NLSSORT Function

Conclusion

NLS_CHARSET_ID and NLS_CHARSET_NAME translate between character set names and numbers, and NLS_CHARSET_DECL_LEN tells how many characters fit in an NCHAR declaration of a given byte length. Together with NLS_DATABASE_PARAMETERS, they tell you exactly how your database stores text.