Character Datatypes with example in Oracle

1)CHAR Datatype
---------------------------------------
•The CHAR datatype in oracle specifies fixed length character string. That is if you specify datatype as COL1 CHAR(10) then regardless of value entered in column COL1 the length of the value will be 10 bytes.

•In fact if you insert a value that is shorter than the column length, then Oracle blank pads (add spaces after the text) the value to column length. If you try to insert a value that is larger than the column length, then Oracle returns an error.

•The default length for a CHAR datatype column is 1 byte and the maximum allowed is 2000 bytes.

•The column length for CHAR datatype can be specified both in bytes and characters. By default if you just CHAR(10) then 10 bytes of column size is specified. If you want to specify the size of CHAR datatype in characters then declare as CHAR(10 CHAR). Then the size of the CHAR datatypes column varies between 1 to 4 bytes based on the database character sets.

•The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte semantics.

2)NCHAR Datatype
--------------------------------------------
•When a column is defined with NCHAR datatype then column length is defined with characters.

•It is a Unicode-only datatype.

•The maximum column size allowed is 2000 bytes.

•If you insert a value that is shorter than the column length, then Oracle blank pads (add spaces after the text) the value to column length.

•CHAR value can't be inserted into an NCHAR column,and also NCHAR value can't be inserted into a CHAR column.

3)NVARCHAR2 Datatype
-----------------------------------------------
•The NVARCHAR2 datatype is a Unicode-only datatype.

•When you create a table with an NVARCHAR2 column, you specify the maximum number of characters it can hold.

•The maximum column size allowed is 4000 bytes.

4)VARCHAR2 Datatype
----------------------------------------------------
•When you create a column with VARCHAR2 datatype, you specify the maximum number of bytes or characters of data that it can hold.

•This minimum length of VARCHAR2 datatype must be at least 1 byte, although the actual string stored is permitted to be a zero-length string ('').

•The maximum length of VARCHAR2 data is 4000 bytes.

5)VARCHAR Datatype
------------------------
•Oracle recommends not to use VARCHAR datatype. Though currently there is no difference between VARCHAR and VARCHAR2 datatype. The VARCHAR datatype is currently synonymous with VARCHAR2.

•Oracle schedule VARCHAR datatype to use separate datatype.

To know the differenece between CHAR, VARCHAR2 and VARCHAR please visit What is the difference between VARCHAR, VARCHAR2 and CHAR data types

Example:
-----------------
In the following example I used all character datatypes to create a table.

SQL> SHOW PARAMETER nls_length_semantics

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE

SQL> CREATE TABLE WITH_ALL_CHAR(char_dt CHAR, char_dt_in_char CHAR(5 CHAR), nchar_dt NCHAR(4), varchar_dt VARCHAR2(10), nvarchar2_dt NVARCHAR2(10));
Table created.

SQL> desc WITH_ALL_CHAR
Name Null? Type
----------------------------------------- -------- ----------------------------
CHAR_DT CHAR(1)
CHAR_DT_IN_CHAR CHAR(5 CHAR)
NCHAR_DT NCHAR(4)
VARCHAR_DT VARCHAR2(10)
NVARCHAR2_DT NVARCHAR2(10)

SQL> select length(CHAR_DT) CHAR_DT, length(CHAR_DT_IN_CHAR) CHAR_DT_IN_CHAR , length(NCHAR_DT) NCHAR_DT ,length(VARCHAR_DT) VARCHAR_DT, length(NVARCHAR2_DT) NVARCHAR2_DT from WITH_ALL_CHAR;

CHAR_DT CHAR_DT_IN_CHAR NCHAR_DT VARCHAR_DT NVARCHAR2_DT
---------- --------------- ---------- ---------- ------------
1 5 4 4 7