Using the TO_CHAR Function

View All Scripts Login to Run Script

Script Name Using the TO_CHAR Function
Description Demonstrates the use of TO_CHAR (character), TO_CHAR (datetime) and TO_CHAR (number).
Area SQL General / SQL Query
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1

A table named empl_temp is created.

CREATE TABLE empl_temp 
  ( 
     employee_id NUMBER(6), 
     first_name  VARCHAR2(20), 
     last_name   VARCHAR2(25), 
     email       VARCHAR2(25), 
     hire_date   DATE DEFAULT SYSDATE, 
     job_id      VARCHAR2(10), 
     clob_column CLOB 
  )

Table created.

Statement 2

The empl_temp table is populated.

INSERT INTO empl_temp 
VALUES(111,'John','Doe','example.com','10-JAN-2015','1001','Experienced Employee')

1 row(s) inserted.

Statement 3

The empl_temp table is populated.

INSERT INTO empl_temp 
VALUES(112,'John','Smith','example.com','12-JAN-2015','1002','Junior Employee')

1 row(s) inserted.

Statement 4

The empl_temp table is populated.

INSERT INTO empl_temp 
VALUES(113,'Johnnie','Smith','example.com','12-JAN-2014','1002','Mid-Career Employee')

1 row(s) inserted.

Statement 5

The empl_temp table is populated.

INSERT INTO empl_temp 
VALUES(115,'Jane','Doe','example.com','15-JAN-2015','1005','Executive Employee')

1 row(s) inserted.

Statement 6

Display the contents of the empl_temp table.

SELECT * FROM empl_temp


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL HIRE_DATE JOB_ID CLOB_COLUMN
111 John Doe example.com 10-JAN-15 1001 Experienced Employee
112 John Smith example.com 12-JAN-15 1002 Junior Employee
113 Johnnie Smith example.com 12-JAN-14 1002 Mid-Career Employee
115 Jane Doe example.com 15-JAN-15 1005 Executive Employee

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	HIRE_DATE	JOB_ID	CLOB_COLUMN
111	John	Doe	example.com	10-JAN-15	1001	Experienced Employee
112	John	Smith	example.com	12-JAN-15	1002	Junior Employee
113	Johnnie	Smith	example.com	12-JAN-14	1002	Mid-Career Employee
115	Jane	Doe	example.com	15-JAN-15	1005	Executive Employee

Statement 7

This statement displays dates by using the long and short formats.

SELECT hire_date "Default",   
       TO_CHAR(hire_date,'DS') "Short",   
       TO_CHAR(hire_date,'DL') "Long"   
FROM empl_temp   
WHERE employee_id IN (111, 112, 115)


Default Short Long
10-JAN-15 1/10/2015 Saturday, January 10, 2015
12-JAN-15 1/12/2015 Monday, January 12, 2015
15-JAN-15 1/15/2015 Thursday, January 15, 2015

3 rows selected.

Default	Short	Long
10-JAN-15	1/10/2015	Saturday, January 10, 2015
12-JAN-15	1/12/2015	Monday, January 12, 2015
15-JAN-15	1/15/2015	Thursday, January 15, 2015

Statement 8

This statement converts CLOB data to the database character set.

SELECT To_char(clob_column) "CLOB_TO_CHAR" 
FROM   empl_temp 
WHERE  employee_id IN ( 111, 112, 115 )


CLOB_TO_CHAR
Experienced Employee
Junior Employee
Executive Employee

3 rows selected.

CLOB_TO_CHAR
Experienced Employee
Junior Employee
Executive Employee

Statement 9

This statement converts NUMBER data to the database character set.

SELECT To_char(employee_id) "NUM_TO_CHAR" 
FROM   empl_temp 
WHERE  employee_id IN ( 111, 112, 113, 115 )


NUM_TO_CHAR
111
112
113
115

4 rows selected.

NUM_TO_CHAR
111
112
113
115

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database