Oracle TO_CHAR (character) Function: A Simple Guide

The TO_CHAR (character) function in Oracle SQL is a conversion function used to convert data from "large object" (CLOB, NCLOB) or "national character" (NCHAR, NVARCHAR2) data types into the standard database character set, VARCHAR2.

Its primary use is to read text from a CLOB or NCHAR column and return it as a regular VARCHAR2 string that can be used by most applications or SQL functions.

What is the TO_CHAR (character) Function in Oracle?

The TO_CHAR(value) function takes a CLOB, NCLOB, NCHAR, or NVARCHAR2 value as input and returns a VARCHAR2 string.

This is different from other TO_CHAR functions, which are used to format DATE or NUMBER values. This version is purely for character set and data type conversion.

Important: Size Limitation

This function returns a VARCHAR2. If the CLOB or NCLOB value you are converting is larger than the maximum size of a VARCHAR2 (e.g., 4000 or 32767 bytes, depending on your database settings), the function will return an error. It does not truncate the data.

TO_CHAR (character) Function Syntax

The syntax for TO_CHAR (character) is very simple:

TO_CHAR(clob_or_nchar_value)

Let's break that down:

• clob_or_nchar_value: The CLOB, NCLOB, NCHAR, or NVARCHAR2 value or column you want to convert to VARCHAR2.

Oracle TO_CHAR (character) Function Examples

Here are two practical examples of how to use TO_CHAR (character).

Example 1: Converting a CLOB Column to VARCHAR2 using TO_CHAR

This is the most common use case. Imagine you have a table with a CLOB column that stores long descriptions, but you only need to select the (smaller) descriptions for a report.

Query:

-- We'll simulate a table 'empl_temp' with a CLOB column
WITH empl_temp AS (
  SELECT 111 AS employee_id, TO_CLOB('Experienced Employee') AS clob_column FROM DUAL UNION ALL
  SELECT 112 AS employee_id, TO_CLOB('Junior Employee') AS clob_column FROM DUAL UNION ALL
  SELECT 115 AS employee_id, TO_CLOB('Executive Employee') AS clob_column FROM DUAL
)
-- The query uses TO_CHAR to read the CLOB as VARCHAR2
SELECT 
  employee_id,
  TO_CHAR(clob_column) AS "CLOB_to_VARCHAR2"
FROM 
  empl_temp
WHERE 
  employee_id IN (111, 112, 115);

Result: (The CLOB data is returned as standard VARCHAR2 text)

EMPLOYEE_ID CLOB_to_VARCHAR2
----------- --------------------
        111 Experienced Employee
        112 Junior Employee
        115 Executive Employee

Example 2: Converting an NCHAR String to VARCHAR2 using TO_CHAR

This example shows how to convert a value from the national character set (NCHAR) to the database character set (VARCHAR2). We use N'' to create an NCHAR literal string.

Query:

SELECT 
  TO_CHAR(N'This is an NCHAR string') AS "Converted_String"
FROM DUAL;

Result:

Converted_String
-----------------------
This is an NCHAR string