Oracle Database - Character DataType

> Database > Oracle Database

1 - What are the Character Data Types?

You can use the following SQL data types to store alphanumeric data:

  • VARCHAR2 and NVARCHAR2 data types store variable-length character literals.
  • NCHAR and NVARCHAR2 data types store variable-length Unicode character data only.
  • CHAR and NCHAR data types store fixed-length character literals.

3 - Choosing Between the Character Data Types

When deciding which data type to use for a column that will store alphanumeric data in a table, consider the following points of distinction:

  • Space usage

To store data more efficiently, use the VARCHAR2 data type. The CHAR data type blank-pads and stores trailing blanks up to a fixed column length for all column values, whereas the VARCHAR2 data type does not add extra blanks as you can see in this article : Oracle Database - Datatype difference between VARCHAR2 and CHAR

  • Comparison semantics

Use the CHAR data type when you require ANSI compatibility in comparison semantics (when trailing blanks are not important in string comparisons). Use the VARCHAR2 when trailing blanks are important in string comparisons.


4 - BYTE and CHAR qualifiers

When you create a table with a CHAR column, by default you supply the column length in bytes. The BYTE qualifier is the same as the default. If you use the CHAR qualifier, for example CHAR(10 CHAR), then you supply the column length in characters. A character is technically a code point of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte semantics. For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter.

5 - Documentation / Reference

  • Sql Developer Help
db/oracle/datatype_character.txt · Last modified: 2017/09/06 19:27 by gerardnico