Summary: in this tutorial, you will learn about the Oracle CHAR
data type which is a fixed-length character string type.
Introduction to Oracle CHAR
data type
The Oracle CHAR
data type allows you to store fixed-length character strings. The CHAR
data type can store a character string with the size from 1 to 2000 bytes.
To define a CHAR
column, you need to specify a string length either in bytes or characters as shown following:
Code language: SQL (Structured Query Language) (sql)CHAR(length BYTE) CHAR(length CHAR)
If you don’t explicitly specify BYTE
or CHAR
followed the length
, Oracle uses the BYTE
by default.
The default value of length
is 1 if you skip it like the following example:
Code language: SQL (Structured Query Language) (sql)column_name CHAR
When you insert or update a fixed-length character string column, Oracle stores the characters as the fixed-length data. It means that if you store a value whose length is less than the maximum length defined in the column, Oracle pads the spaces to the character string up to the maximum length. In case you insert a value whose length is larger than the column, Oracle returns an error.
Oracle uses blank-padded comparison semantics for comparing CHAR
values.
Oracle CHAR
examples
Let’s take a look some examples to understand how the CHAR
data type works.
A) Space usage example
First, create a new table named t
that consists of a CHAR
column (x
) and VARCHAR2
column (y
). The length of each column is 10 bytes.
CREATE TABLE t (
x CHAR(10),
y VARCHAR2(10)
);
Code language: SQL (Structured Query Language) (sql)
Second, insert a new row into the t
table with the same data for both x
and y
columns:
INSERT INTO t(x, y )
VALUES('Oracle', 'Oracle');
Code language: SQL (Structured Query Language) (sql)
Third, verify the insert by using the following query:
SELECT
*
FROM
t
Code language: SQL (Structured Query Language) (sql)

The following statement retrieves data from the t
table:
SELECT
x,
DUMP(x),
y,
DUMP(y)
FROM
t;
Code language: SQL (Structured Query Language) (sql)

In this example, we used the
function to return the detailed information on DUMP()
x
and y
columns:
The string Oracle
takes 6 bytes. However, Oracle padded 4 more spaces on the right of the string to make its length 10 bytes for the x
column. It is not the case for the y
column because the data type of y
column is a variable-length character string (VARCHAR2
).
It is more clear if you use the
function to get the number of bytes used by the LENGTHB()
x
and y
columns:
SELECT
LENGTHB(x),
LENGTHB(y)
FROM
t;
Code language: SQL (Structured Query Language) (sql)

B) Characters comparison example
The following statements return the same result:
SELECT * FROM t WHERE x = 'Oracle';
SELECT * FROM t WHERE y = 'Oracle';
Code language: SQL (Structured Query Language) (sql)
However, if you use bind variables, the effect is different. Consider the following example:
Code language: SQL (Structured Query Language) (sql)SQL> variable v varchar2(10) SQL> exec :v := 'Oracle'; PL/SQL procedure successfully completed.
In this code block, we declared v
as a bind variable with the VARCHAR2
data type.
Now, we use v
as an input to compare against the the x
column:
SQL> select * from t where x = :v;
no rows selected
Code language: SQL (Structured Query Language) (sql)
The statement returned an empty result set.
The following query uses the v
variable to compare with the y
column:
SQL> select * from t where y = :v;
X Y
---------- ----------
Oracle Oracle
Code language: SQL (Structured Query Language) (sql)
It returned a row as expected.
This is because when comparing the string of character types with unequal length, Oracle uses non-blank-padding semantics.
To make it work, you need to use the RTRIM()
function to strip spaces from the CHAR
data before comparing it with the input string as follows:
SQL> select * from t where rtrim(x) = :v;
X Y
---------- ----------
Oracle Oracle
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned about the Oracle CHAR
data type and understood the behaviors of the CHAR
columns in terms of space usages and character comparisons.