Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle CHAR

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)

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

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) );

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');

Third, verify the insert by using the following query:

SELECT * FROM t
Oracle CHAR example

The following statement retrieves data from the t table:

SELECT x, DUMP(x), y, DUMP(y) FROM t;
Oracle CHAR DUMP example

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;
Oracle CHAR lengthb example

B) Characters comparison example

The following statements return the same result:

SELECT * FROM t WHERE x = 'Oracle'; SELECT * FROM t WHERE y = 'Oracle';

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.

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

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

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

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.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Identity Column
Next Oracle NCHAR

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.