Oracle CHAR

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:

CHAR(length BYTE)
CHAR(length CHAR)
Code language: SQL (Structured Query Language) (sql)

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:

column_name CHAR
Code language: SQL (Structured Query Language) (sql)

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 DUMP() function to return the detailed information on 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 LENGTHB() function to get the number of bytes used by the 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:

SQL> variable v varchar2(10)
SQL> exec :v := 'Oracle';

PL/SQL procedure successfully completed.
Code language: SQL (Structured Query Language) (sql)

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.