Morgan's Library Oracle Database Convertion Functions Examples Sample Code Demos Tutorials SQL PL/SQL

Oracle Conversion Functions
Version 11.2.0.3

Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions

Note: Functions for converting to date, numeric, string, and timestamp data types can be found through the related links.

ASCIISTR

Converts An ASCII String To An ASCII String In The Database's Character Set

ASCIISTR(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;

SELECT ASCIISTR(CHR(128) || 'Hello' || CHR(255))
FROM dual;

BFILENAME

Returns a BFILE from a combination of a directory and a file name

BFILENAME(directory IN VARCHAR2, filename IN VARCHAR2) RETURN BFILE;

DECLARE
src_file BFILE;
BEGIN
src_file := bfilename('CTEMP', 'myfile.txt');
END;
/

BIN_TO_NUM

Converts a bit vector to a number

BIN_TO_NUM(<value>,<value>,....) RETURN NUMBER;

SELECT BIN_TO_NUM(1, 0, 1, 0) FROM dual;

CAST

Converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value

CAST(<string_or_column> AS <DATATYPE>)

SELECT CAST(15402 AS VARCHAR2(30))
FROM dual;

CHARTOROWID

Converts a value from CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to ROWID datatype

CHARTOROWID(str IN VARCHAR2) RETURN ROWID;

conn hr/hr

SELECT rowid
FROM employees;

SELECT last_name
FROM employees
WHERE ROWID = CHARTOROWID('AAAQvRAAFAAAABYAAp');

COMPOSE

Convert a string in any data type to a Unicode string

COMPOSE(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET ch%charset;

Unistring Value	Resulting character
unistr('\0300')	grave accent (`)
unistr('\0301')	acute accent (´)
unistr('\0302')	circumflex (ˆ)
unistr('\0303')	tilde (~)
unistr('\0308')	umlaut (¨)

SELECT 'ol' || COMPOSE('e' || UNISTR('\0301')) FROM dual;

CONVERT

Converts a character string from one character set to another

Overload 1

CONVERT(src IN VARCHAR2 CHARACTER SET ANY_CS, destcset IN VARCHAR2)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET;

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E', 'US7ASCII')
FROM dual;

Overload 2

CONVERT(
src IN VARCHAR2 CHARACTER SET ANY_CS,
destcset IN VARCHAR2,
srccset IN VARCHAR2)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET;

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E','US7ASCII', 'WE8ISO8859P1')
FROM dual;

Overload 3

CONVERT(srcstr IN CLOB CHARACTER SET ANY_CS, dstcsn IN VARCHAR2)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;

TBD

Overload 4

CONVERT(
srcstr IN CLOB CHARACTER SET ANY_CS,
dstcsn IN VARCHAR2,
srccsn IN VARCHAR2)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;

TBD

DECOMPOSE

Converts a unicode string to a string

DECOMPOSE(ch IN VARCHAR2 CHARACTER SET ANY_CS, canmode IN VARCHAR2 DEFAULT 'CANONICAL')
RETURN VARCHAR2 CHARACTER SET ch%charset;

SELECT DECOMPOSE('ol' || COMPOSE('e' || UNISTR('\0301')))
FROM dual;

HEXTORAW

Converts char containing hexadecimal digits in the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 character set to a raw value

HEXTORAW(c IN VARCHAR2) RETURN RAW;

CREATE TABLE test (
raw_col RAW(10));

desc test

INSERT INTO test VALUES (HEXTORAW('7D'));

SELECT * FROM test;

NUMTODSINTERVAL

Converts a number to an INTERVAL DAY TO SECOND literal

NUMTODSINTERVAL(numerator IN NUMBER, units IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN INTERVAL DAY TO SECOND;

SELECT NUMTODSINTERVAL(120, 'DAY') FROM dual;

SELECT NUMTODSINTERVAL(1200, 'HOUR') FROM dual;

SELECT NUMTODSINTERVAL(12000, 'MINUTE') FROM dual;

SELECT NUMTODSINTERVAL(120000, 'SECOND') FROM dual;

NUMTOYMINTERVAL

Converts n to an INTERVAL YEAR TO MONTH literal

NUMTOYMINTERVAL(numerator IN NUMBER, units IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN INTERVAL YEAR TO MONTH;

conn hr/hr

SELECT last_name, hire_date, salary, SUM(salary)
OVER (ORDER BY hire_date
RANGE NUMTOYMINTERVAL(1, 'YEAR') PRECEDING) AS t_sal
FROM employees;

RAWTOHEX

Converts raw to a character value containing its hexadecimal equivalent

See also UTL_RAW

RAWTOHEX(r IN RAW) RETURN VARCHAR2;

CREATE TABLE test (
raw_col RAW(10));

desc test

INSERT INTO test VALUES (HEXTORAW('7D'));

SELECT * FROM test;
SELECT RAWTOHEX(raw_col) HEXVAL
FROM test;

RAWTONHEX

Converts raw to an NVARCHAR2 character value containing its hexadecimal equivalent

RAWTOHEX(r IN RAW) RETURN NVARCHAR2;

col dumpcol format a30

SELECT RAWTONHEX(raw_col) HEXVAL, dump(raw_col) dumpcol
FROM test;

REFTOHEX

Converts argument expr to a character value containing its hexadecimal equivalent. expr must return a REF

REFTOHEX(<expr>);

conn oe/oe

CREATE TABLE warehouse_table OF warehouse_typ
(PRIMARY KEY (warehouse_id));

CREATE TABLE location_table (
location_number NUMBER,
building REF warehouse_typ SCOPE IS warehouse_table);

INSERT INTO warehouse_table VALUES (1, 'Downtown', 99);

INSERT INTO location_table SELECT 10, REF(w)
FROM warehouse_table w;

SELECT REFTOHEX(building) FROM location_table;

DROP TABLE warehouse_table PURGE;

ROWIDTOCHAR

Converts a rowid value to VARCHAR2 datatype

ROWIDTOCHAR(str IN ROWID) RETURN VARCHAR2;

SELECT COUNT(*)
FROM servers;

SELECT rowid
FROM servers
WHERE rownum < 11;

SELECT ROWID
FROM servers
WHERE ROWIDTOCHAR(ROWID) LIKE '%AAB%';

ROWIDTONCHAR

Converts a rowid value to NVARCHAR2 datatype

ROWIDTONCHAR(str IN ROWID) RETURN NVARCHAR2;

See ROWIDTOCHAR demo above

SCN_TO_TIMESTAMP

Returns the approximate Timestamp for an SCN

SCN_TO_TIMESTAMP(<scn>);

col current_scn format 99999999999999999999

SELECT current_scn
FROM v$database;

SELECT SCN_TO_TIMESTAMP(10912156206286)
FROM dual;

TIMESTAMP_TO_SCN

Returns the approximate SCN for a timestamp

TIMESTAMP_TO_SCN(<timestamp>)

col current_scn format 99999999999999999999

SELECT current_scn
FROM v$database;

SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) CURRENT_SCN
FROM dual;

TO_BINARY_DOUBLE

Converts a Value to the BINARY_DOUBLE Data Type
Overload 1

TO_BINARY_DOUBLE(right IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;

See TO_NUMBER demo, below.

Overload 2

TO_BINARY_DOUBLE(
left IN VARCHAR2 SET ANY_CS, FORMAT,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN BINARY_DOUBLE;

See TO_NUMBER demo, below.

Overload 3

TO_BINARY_DOUBLE(
left IN VARCHAR2 SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
params IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN BINARY_DOUBLE;

See TO_NUMBER demo, below.

TO_BINARY_FLOAT

Converts a Value to the BINARY_FLOAT Data Type

TO_BINARY_FLOAT(right IN BINARY_FLOAT) RETURN BINARY_FLOAT

See TO_NUMBER demo, below.

Overload 2

TO_BINARY_FLOAT(
left IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN BINARY_FLOAT;

See TO_NUMBER demo, below.

Overload 3

TO_BINARY_FLOAT(
left IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
params IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN BINARY_FLOAT;

See TO_NUMBER demo, below.

TO_BLOB

Converts a RAW value into a BLOB

TO_BLOB(right IN RAW) RETURN BLOB;

DECLARE
b BLOB;
BEGIN
b := TO_BLOB('0F0F0F');
END;
/

TO_CHAR

Convert Datatype To String
Overload 1

TO_CHAR(right IN VARCHAR2) RETURN VARCHAR2

SELECT TO_CHAR('Dan Morgan') FROM dual;

Overload 2

TO_CHAR(left IN DATE, right IN VARCHAR2) RETURN VARCHAR2

SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS') FROM dual;

Overload 3
Convert NUMBER to String

TO_CHAR(left IN NUMBER) RETURN VARCHAR2

SELECT TO_CHAR(123) FROM dual;

Overload 3
Convert NUMBER to HEX

TO_CHAR(left IN NUMBER, right IN VARCHAR2) RETURN VARCHAR2

SELECT TO_CHAR(1048576,'XXXXXXXX')
FROM dual;

Overload 4

TO_CHAR(left IN MLSLABEL, right IN VARCHAR2) RETURN VARCHAR2

TBD

Converts DATE and TIMESTAMP to VARCHAR2 with the specified format

The "X" in the ROUND and TRUNC column indicates that these symbols with these functions

Overload 5

TO_CHAR(left IN date, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;

-- before running these demos
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Symbol	Description	ROUND	TRUNC
AD A.D.	Returns "AD" if the year is greater than 0000: Otherwise "BC"	X
	SELECT TO_CHAR(TO_DATE('31-DEC-0001')+1000, 'BC') FROM dual; TO -- AD SELECT TO_CHAR(TO_DATE('31-DEC-0001')-400, 'BC') FROM dual; TO -- 00
AM A.M.	Returns "AM" if the time is less than noon: Otherwise "PM"	X
	SELECT TO_CHAR(TO_DATE('01-JAN-2009'), 'AM') FROM dual; TO -- AM
BC B.C.	Returns "BC" if the year is less than 0000: Otherwise "AD"	X
	SELECT TO_CHAR(TO_DATE('31-DEC-0001')-1000, 'BC') FROM dual; TO -- BC SELECT TO_CHAR(TO_DATE('31-DEC-0001')-400, 'BC') FROM dual; TO -- 00
CC	Century (Same as SCC)	X	X
	SELECT TO_CHAR(SYSDATE, 'CC') FROM dual; TO_CHAR(SYSDATE,'CC') --------------------------------------------- 21
D	Starting day of the week	X	X
	SELECT SYSDATE, TO_CHAR(SYSDATE, 'D') FROM dual; SYSDATE TO -------------------- -- 06-AUG-2009 22:12:13 06
DD	Day	X	X
	SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD') FROM dual; SYSDATE TO -------------------- -- 06-AUG-2009 22:12:34 06
DDD	Day	X	X
	SELECT TO_CHAR(sysdate, 'DDD') FROM dual; TO_CHAR(SYSDATE,'DDD') ---------------------------------------------- 093
DAY	Starting day of the week	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'DAY') FROM dual; TO_CHAR(SYSDATE,'DAY') ---------------------------------------------- WEDNESDAY
DL	Day and long date format	X
	SELECT TO_CHAR(SYSDATE, 'DL') FROM dual; TO_CHAR(SYSDATE,'DL') ----------------------------- Thursday, August 06, 2009
DS	Short date format	X
	SELECT TO_CHAR(SYSDATE, 'DS') FROM dual; TO_CHAR(SY ---------- 8/6/2009
DY	Current day of the week	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'DY') FROM dual; SYSDATE TO_ -------------------- --- 07-AUG-2009 20:53:31 FRI
E	Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).	X
	TBD
EE	Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).	X
	TBD
FM	Returns a value with no leading or trailing blanks. Not my experience.	X
	SELECT TO_CHAR(SYSDATE, 'Day, Month DD, YYYY') FROM dual; TO_CHAR(SYSDATE,'DAY,MONTHDD, ----------------------------- Saturday , August 08, 2009 SELECT TO_CHAR(SYSDATE, 'FMDay, Month DD, YYYY') FROM dual; TO_CHAR(SYSDATE,'FMDAY,MONTHD ----------------------------- Saturday, August 8, 2009
FX	Requires exact matching between the character data and the format model	X
	SELECT TO_CHAR(SYSDATE, 'Day, Month DD, YYYY') FROM dual; TO_CHAR(SYSDATE,'DAY,MONTHDD, ----------------------------- Saturday , August 08, 2009 SELECT TO_CHAR(SYSDATE, 'FXDay, Month DD, YYYY') FROM dual; TO_CHAR(SYSDATE,'FMDAY,MONTHD ----------------------------- Saturday, August 8, 2009
HH	Truncate Date-Time to Hours	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'HH') FROM dual; SYSDATE TO_CHAR(SYSDATE,'HH') --------------------- --------------------- 06-AUG-2009 22:35:02 10 SELECT sysdate, TRUNC(SYSDATE, 'HH') FROM dual; SYSDATE TRUNC(SYSDATE,'HH') -------------------- -------------------- 06-AUG-2009 21:35:02 06-AUG-2009 21:00:00
HH12	Hours (12 hour clock)	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'HH12') FROM dual; SYSDATE TO -------------------- -- 06-AUG-2009 21:35:29 09
HH24	Hours (24 hour clock)	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'HH24') FROM dual; SYSDATE TO -------------------- -- 06-AUG-2009 21:35:50 21
I	ISO Year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'I') FROM dual; SYSDATE T -------------------- - 06-AUG-2009 21:36:37 9
IW	Same day of the week as the first day of the ISO year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'IW') FROM dual; SYSDATE TO -------------------- -- 06-AUG-2009 22:08:57 32
IY	ISO Year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'IY') FROM dual; SYSDATE TO -------------------- -- 06-AUG-2009 22:10:04 09
IYY	ISO 3 Digit Year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'IYY') FROM dual; SYSDATE TO_ -------------------- --- 07-AUG-2009 20:51:59 009
IYYY	ISO Year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'IYYY') FROM dual; SYSDATE TO_C -------------------- ---- 07-AUG-2009 20:51:36 2009
J	Julian Day	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'J') FROM dual; SYSDATE TO_CHAR -------------------- ------- 07-AUG-2009 20:51:18 2455051
JSP	Julian Day
	SELECT TO_CHAR(TO_DATE(4242,'J'), 'JSP') as converted_form FROM dual; CONVERTED_FORM ----------------------------------- FOUR THOUSAND TWO HUNDRED FORTY-TWO
MI	Minutes	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'MI') FROM dual; SYSDATE TO -------------------- -- 06-AUG-2009 22:08:20 08
MM	Month (rounds up on the sixteenth day)	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'MM') FROM dual; SYSDATE TO -------------------- -- 07-AUG-2009 20:25:43 08
MON	Month abreviation	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'MON') FROM dual; SYSDATE TO_ -------------------- --- 07-AUG-2009 20:26:09 AUG
MONTH	Month name spelled out	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'MONTH') FROM dual; SYSDATE TO_CHAR(S -------------------- --------- 07-AUG-2009 20:26:38 AUGUST
PM P.M.	Returns "PM" if the time is after noon: Otherwise "AM"	X
	SELECT TO_CHAR(TO_DATE('01-JAN-2009') + 13/24, 'AM') FROM dual; SELECT TO_CHAR(TO_DATE('01-JAN-2009') + 13/24, 'PM') FROM dual; TO -- PM
Q	Quarter	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'Q') FROM dual; SYSDATE T -------------------- - 07-AUG-2009 20:33:47 3 SELECT sysdate, TRUNC(SYSDATE, 'Q') FROM dual SYSDATE TRUNC(SYSDATE,'Q') -------------------- -------------------- 07-AUG-2009 20:34:02 01-JUL-2009 00:00:00
RM	Roman Numeral Month	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'RM') FROM dual; SYSDATE TO_C -------------------- ---- 07-AUG-2009 20:36:54 VIII
RR	Allows storage of 20th century dates in the 21st century using only two digits	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'RR') FROM dual; SYSDATE TO -------------------- -- 07-AUG-2009 20:37:19 09
RRRR	Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR.	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'RRRR') FROM dual; SYSDATE TO_C -------------------- ---- 07-AUG-2009 20:37:45 2009
SCC	Century (same as CC)	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'SCC') FROM dual; SYSDATE TO_ -------------------- --- 07-AUG-2009 20:43:10 21
SP	Spelled out	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'SP') FROM dual; SQL> SELECT sysdate, TO_CHAR(SYSDATE, 'SP') FROM dual; SELECT sysdate, TO_CHAR(SYSDATE, 'SP') FROM dual * ERROR at line 1: ORA-01821: date format not recognized SQL> ed Wrote file afiedt.buf 1* SELECT sysdate, TO_CHAR(SYSDATE, 'MMSP') FROM dual SQL> / SYSDATE TO_CHAR(SYSDATE,'MMSP') -------------------- ---------------------------------- 03-JUL-2010 07:10:46 SEVEN SQL> ed Wrote file afiedt.buf 1* SELECT sysdate, TO_CHAR(SYSDATE, 'DDSP') FROM dual SQL> / SYSDATE TO_CHAR(SYSDATE,'DDSP') -------------------- ---------------------------------- 03-JUL-2010 07:11:00 THREE SQL> ed Wrote file afiedt.buf 1* SELECT sysdate, TO_CHAR(SYSDATE, 'YYSP') FROM dual SQL> / SYSDATE TO_CHAR(SYSDATE,'YYSP') -------------------- ---------------------------------- 03-JUL-2010 07:11:08 TEN
SPTH	Spelled out in ordinal format	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'SP') FROM dual; SQL> SELECT sysdate, TO_CHAR(SYSDATE, 'SP') FROM dual; SELECT sysdate, TO_CHAR(SYSDATE, 'SP') FROM dual * ERROR at line 1: ORA-01821: date format not recognized SQL> ed Wrote file afiedt.buf 1* SELECT sysdate, TO_CHAR(SYSDATE, 'MMSPTH') FROM dual SQL> / SYSDATE TO_CHAR(SYSDATE,'MMSPTH') -------------------- ---------------------------------- 03-JUL-2010 07:10:46 SEVENTH SQL> ed Wrote file afiedt.buf 1* SELECT sysdate, TO_CHAR(SYSDATE, 'DDSPTH') FROM dual SQL> / SYSDATE TO_CHAR(SYSDATE,'DDSPTH') -------------------- ---------------------------------- 03-JUL-2010 07:11:00 THIRD SQL> ed Wrote file afiedt.buf 1* SELECT sysdate, TO_CHAR(SYSDATE, 'YYSPTH') FROM dual SQL> / SYSDATE TO_CHAR(SYSDATE,'YYSPTH') -------------------- ---------------------------------- 03-JUL-2010 07:11:08 TENTH
SS	Seconds	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'SS') FROM dual; SYSDATE TO -------------------- -- 07-AUG-2009 20:45:03 03
SSSSS	Seconds past midnight	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'SSSSS') FROM dual; SYSDATE TO_CH -------------------- ----- 07-AUG-2009 20:45:44 74744
SYEAR	Year spelled out	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'SYEAR') FROM dual; SYSDATE TO_CHAR(SYSDATE,'SYEAR') -------------------- ------------------------ 07-AUG-2009 20:48:10 TWO THOUSAND NINE
SYYYY	Four digit year. Prefixes BC with a minus sign	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'SYYYY') FROM dual; SYSDATE TO_CH -------------------- ----- 07-AUG-2009 21:15:33 2009
TS	Short time format	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'TS') FROM dual; SYSDATE TO_CHAR(SYS -------------------- ----------- 07-AUG-2009 21:23:48 9:23:48 PM
W	Week number in the month	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'W') FROM dual; SYSDATE T -------------------- - 07-AUG-2009 20:57:01 1
WW	Week of the year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'WW') FROM dual; SYSDATE TO -------------------- -- 07-AUG-2009 21:01:44 32
X	Local Radix Character	X
	SELECT TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MM:SSXFF') FROM dual; * TO_CHAR(SYSTIMESTAMP,'DD-MON-Y ------------------------------ 07-AUG-2009 09:08:08.156000
Y	One Digit Year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'Y') FROM dual; SYSDATE T -------------------- - 07-AUG-2009 21:04:06 9
YEAR	Year name spelled out	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'YEAR') FROM dual; SYSDATE TO_CHAR(SYSDATE,'YEAR') -------------------- ----------------------- 03-JUL-2010 07:01:42 TWENTY TEN
YY	Two Digit Year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'YY') FROM dual; SYSDATE TO -------------------- -- 07-AUG-2009 21:04:20 09
YYY	Three Digit Year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'YYY') FROM dual; SYSDATE TO_ -------------------- --- 07-AUG-2009 21:04:32 009
YYYY	Four Digit Year	X	X
	SELECT sysdate, TO_CHAR(SYSDATE, 'YYYY') FROM dual; SYSDATE TO_C -------------------- ---- 07-AUG-2009 21:04:42 2009

CREATE TABLE t (
datecol1 DATE,
datecol2 DATE);

INSERT INTO t (datecol1, datecol2) VALUES (SYSDATE, SYSDATE);

col col1 format a30
col col2 format a20
col col3 format a20
col "Financial Quarter" format a20

SELECT TO_CHAR(datecol1, 'DAY-MONTH-YY') COL1,
       TO_CHAR(datecol2, 'D-MM/YYYY HH24:MI:SS') COL2,
       TO_CHAR(datecol2, 'YYYY-MON-DD HH24') COL3,
       TO_CHAR(datecol2, 'Q') "Financial Quarter"
FROM t;

Demo of FM Formatting From Tom Kyte (Oracle Magazine 3-4/2004)

SELECT TO_CHAR(dt, 'HH:MI AM') A,
TO_CHAR(dt, 'FMHH:MI AM') B,
TO_CHAR(dt, 'FMHHFM:MI AM') C
FROM (SELECT TO_DATE('09:01 am', 'HH:MI AM') dt FROM dual);

Overload 6

TO_CHAR(left IN NUMBER, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 7

TO_CHAR(left IN TIME, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 8

TO_CHAR(left IN TIME, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 9

TO_CHAR(left IN TIME WITH TIME ZONE, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 10

TO_CHAR(left IN TIME WITH TIME ZONE, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 11

TO_CHAR(left IN TIMESTAMP, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 12

TO_CHAR(left IN TIMESTAMP, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 13

TO_CHAR(left IN TIMESTAMP WITH TIME ZONE, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;

TBD

Overload 14

TO_CHAR(left IN TIMESTAMP WITH TIME ZONE, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 15

TO_CHAR(left IN TIMESTAMP WITH LOCAL TIME ZONE, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;

TBD

Overload 16

TO_CHAR(left IN TIMESTAMP WITH LOCAL TIME ZONE, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 17

TO_CHAR(left IN INTERVAL YEAR TO MONTH, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;

TBD

Overload 18

TO_CHAR(left IN INTERVAL YEAR TO MONTH, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 19

TO_CHAR(left IN INTERVAL DAY TO SECOND, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;

TBD

Overload 20

TO_CHAR(left IN INTERVAL DAY TO SECOND, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 21

TO_CHAR(left IN BINARY_FLOAT, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 22

TO_CHAR(left IN BINARY_DOUBLE, format IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 23

TO_CHAR(left IN BINARY_FLOAT, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 24

TO_CHAR(left IN BINARY_DOUBLE, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;

TBD

TO_CLOB

Overload 1

TO_CLOB(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN CLOB;

SELECT TO_CLOB('Some value')
FROM dual;

Overload 2

TO_CLOB(cl IN CLOB CHARACTER SET ANY_CS) RETURN CLOB;

SELECT TO_CLOB('Some value')
FROM dual;

TO_DATE

Convert a string with default format to a date
Overload 1

TO_DATE(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN DATE;

SELECT TO_DATE('01-JAN-2004') FROM dual;

Convert a string with non-default format to a date
Overload 2

TO_DATE(
left IN VARCHAR2 CHARACTER SET ANY_CS,
right IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN DATE;

SELECT TO_DATE('01/01/2004', 'MM/DD/YYYY') FROM dual;

Convert A String With A Non-Default Format And Specify The Language
Overload 2

SELECT TO_DATE('January 12, 2005, 11:03 A.M.', 'MONTH DD, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM dual;

Convert A String To 24 Hour Time
Overload 2

SELECT TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS') FROM dual;

Overload 3

TO_DATE(left IN NUMBER, right IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN DATE;

TBD

Convert A String With A Non-Default Format And Specify The Language

Overload 4

TO_DATE(
left IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
parms IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN DATE;

ALTER SESSION SET NLS_TERRITORY = 'JAPAN';

SELECT TO_DATE('January 12, 2005, 11:03 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

TO_DSINTERVAL

Converts A String To An INTERVAL DAY TO SECOND DataType

Overload 1

TO_DSINTERVAL(
right IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN INTERVAL DAY TO SECOND;

conn hr/hr

SELECT employee_id, last_name
FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00') <= DATE '1990-01-01';

Overload 2

TO_DSINTERVAL(
right IN VARCHAR2 CHARACTER SET ANY_CS,
parms IN VARCHAR2 CHARACTER SET RIGHT%CHARSET)
RETURN INTERVAL DAY TO SECOND;

conn hr/hr

SELECT employee_id, last_name
FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00') <= DATE '1990-01-01';

TO_LOB

Converts LONG or LONG RAW values in the column long_column to LOB values

TO_LOB(long_column) RETURN LOB

desc user_triggers

CREATE TABLE lobtest (
testcol CLOB);

INSERT INTO lobtest
SELECT TO_LOB(trigger_body)
FROM user_triggers;

TO_MULTI_BYTE

Returns char with all of its single-byte characters converted to their corresponding multibyte characters

TO_MULTI_BYTE(c IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET c%CHARSET;

-- must be run in a UTF8 database to see the difference
SELECT dump('A') FROM dual;

SELECT dump(TO_MULTI_BYTE('A')) FROM dual;

TO_NCHAR

Overload 1

TO_NCHAR(right IN NVARCHAR2) RETURN NVARCHAR2;

SELECT TO_NCHAR('ABC') FROM dual;

Overload 2

TO_NCHAR(left IN DATE, format IN NVARCHAR2) RETURN NVARCHAR2;

conn oe/oe

SELECT TO_NCHAR(order_date)
FROM orders
WHERE order_status > 9;

Overload 3

TO_NCHAR(left IN NUMBER, format IN NVARCHAR2) RETURN NVARCHAR2;

SELECT TO_NCHAR(1048576) FROM dual;

Overload 4

TO_NCHAR(left IN DATE, format IN NVARCHAR2, parms IN NVARCHAR2) RETURN NVARCHAR2;

conn oe/oe

SELECT TO_NCHAR(order_date, 'YYYY-MON-DAY')
FROM orders
WHERE order_status > 9;

Overload 5

TO_NCHAR(left IN NUMBER, format IN NVARCHAR2, parms IN NVARCHAR2) RETURN NVARCHAR2;

TBD

Overload 6

TO_NCHAR(left IN BINARY_FLOAT, format IN NVARCHAR2) RETURN NVARCHAR2;

TBD

Overload 7

TO_NCHAR(left IN BINARY_DOUBLE, format IN NVARCHAR2) RETURN NVARCHAR2;

TBD

Overload 8

TO_NCHAR(left IN BINARY_FLOAT, format IN NVARCHAR2, parms IN NVARCHAR2) RETURN NVARCHAR2;

TBD

Overload 9

TO_NCHAR(left IN BINARY_DOUBLE, format IN NVARCHAR2, parms IN NVARCHAR2) RETURN NVARCHAR2;

TBD

TO_NCLOB

Converts CLOB values in a LOB column or other character strings to NCLOB
Overload 1

TO_NCLOB(cl IN CLOB CHARACTER SET ANY_CS) RETURN NCLOB;

TBD

Overload 2

TO_NCLOB(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NCLOB

CREATE TABLE nclob_test(
nclobcol NCLOB);

desc nclob_test

INSERT INTO nclob_test
(nclobcol)
VALUES
(TO_NCLOB('Convert this text into the NCLOB data type'));

TO_NUMBER

Converts a NUMBER to the NUMBER
Overload 1

TO_NUMBER(right IN NUMBER) RETURN NUMBER;

SELECT TO_NUMBER(123) FROM dual;

Converts a string to the NUMBER data type

Overload 2

TO_NUMBER(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NUMBER;

CREATE TABLE test (
testcol VARCHAR2(10));

INSERT INTO test VALUES ('12345.67');

SELECT TO_BINARY_DOUBLE(testcol) BIN_DOUBLE, TO_BINARY_FLOAT(testcol) BIN_FLOAT, TO_NUMBER(testcol) NMBR
FROM test;

Converts a HEX number to FLOAT

Overload 3

TO_NUMBER(
right IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN NUMBER;

SELECT TO_NUMBER('0A', 'XX')
FROM dual;

SELECT TO_NUMBER('1F', 'XX')
FROM dual;

SELECT TO_NUMBER(100000, 'XXXXXXXX')
FROM dual;

Overload 4

TO_NUMBER(
right IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
parms IN VARCHAR2 CHARACTER SET LEFT%CHARSET) RETURN NUMBER;

TBD

Strings with trailing signs

SELECT TO_NUMBER('20-') FROM dual;

SELECT TO_NUMBER('20-', '99,999.99MI') FROM dual;

TO_RAW

Converts BLOB values in a LOB column, or other character strings, to RAW

TO_RAW(right IN BLOB) RETURN RAW;

DECLARE
b BLOB;
r RAW(32);
BEGIN
b := TO_BLOB('0F0F0F');

r := TO_RAW(b);
END;
/

TO_SINGLE_BYTE

Returns char with all of its multibyte characters converted to their corresponding single-byte characters

TO_SINGLE_BYTE(c IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET c%CHARSET;

-- must be run in a UTF8 database to see the difference

SELECT TO_SINGLE_BYTE(CHR(15711393))
FROM dual;

TO_TIMESTAMP

Overload 1

TO_TIMESTAMP(right IN VARCHAR2) RETURN TIMESTAMP_UNCONSTRAINED;

SELECT TO_TIMESTAMP('01-JUL-2009 11:00:00') FROM dual;

Overload 2

TO_TIMESTAMP(
left IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
parms IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN TIMESTAMP;

TBD

Overload 3

TO_TIMESTAMP(
left IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN TIMESTAMP;

SELECT TO_TIMESTAMP('2009-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') FROM dual;

Overload 4

TO_TIMESTAMP(right IN TIMESTAMP WITH TIME ZONE) RETURN TIMESTAMP;

TBD

Overload 5

TO_TIMESTAMP(right IN DATE) RETURN TIMESTAMP

SELECT TO_TIMESTAMP(TO_DATE('01-JUL-2010 01:01:01')) FROM dual;

Overload 6

TO_TIMESTAMP(arg IN TIMESTAMP WITH LOCAL TIME ZONE) RETURN TIMESTAMP;

TBD

To Timestamp with Time Zone

Symbol	Description	Round	Trunc
FF [1...9]	Fractional Seconds	X
	SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF1') FROM dual; SYSTIMESTAMP TO_CHAR(S ------------------------------------ --------- 06-AUG-09 10.19.35.351710 PM -07:00 3 SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF3') FROM dual; SYSTIMESTAMP TO_CHAR(S ------------------------------------ --------- 06-AUG-09 10.21.22.927176 PM -07:00 927 SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF5') FROM dual; SYSTIMESTAMP TO_CHAR(S ------------------------------------ --------- 07-AUG-09 10.20.52.734000 AM -07:00 73400 SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF7') FROM dual; SYSTIMESTAMP TO_CHAR(S ------------------------------------ --------- 07-AUG-09 10.22.00.671000 AM -07:00 6710000
TZD	Timestamp with daylight savings time	X
	SELECT TO_CHAR(TO_TIMESTAMP_TZ('2009-03-15 13:27:58 CET', 'YYYY-MM-DD HH24:MI:SS TZR'), 'YYYY-MM-DD HH24:MI:SS TZR TZD') FROM dual; TO_CHAR(TO_TIMESTAMP_TZ('2009-03-1513:27:58CET','YYYY-MM-DD ----------------------------------------------------------- 2009-03-15 13:27:58 CET CET SELECT TO_TIMESTAMP_TZ('6/15/2009 7:29 AM PDT','MM/DD/YYYY HH:MI AM TZD') FROM dual; TO_TIMESTAMP_TZ('7/31/097:29AMCDT','MM/DD/YYHH:MIAMTZD') --------------------------------------------------------------------------- 31-JUL-09 07.29.00.000000000 AM -04:00
TZH	Time zone hour	X
	SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZH') FROM dual; SYSTIMESTAMP TO_ ----------------------------------- --- 07-AUG-09 09.27.38.296000 PM -07:00 -07
TZM	Time zone minute	X
	SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZM') FROM dual; SYSTIMESTAMP TO ----------------------------------- -- 07-AUG-09 09.27.04.390000 PM -07:00 00
TZR	Time zone region	X
	SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZR') FROM dual; SYSTIMESTAMP TO_CHAR(SYSTIMESTAMP,'TZR') ----------------------------------- ------------------------ 07-AUG-09 09.28.27.500000 PM -07:00 -07:00 SELECT TO_CHAR( TO_TIMESTAMP_TZ('2009-03-15 13:27:58 CET', 'YYYY-MM-DD HH24:MI:SS TZR'), 'YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM dual; TO_CHAR(TO_TIMESTAMP_TZ('2 -------------------------- 2009-03-15 13:27:58 +01:00

TO_TIMESTAMP_TZ

Converts a string to an Timestamp with Timezone Data Type
Overload 1

TO_TIMESTAMP(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN TIMESTAMP WITH TIME ZONE;

TBD

Overload 2

TO_TIMESTAMP(
left IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
parms IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN TIMESTAMP WITH TIME ZONE;

TBD

Overload 3

TO_TIMESTAMP(left IN VARCHAR2, format IN VARCHAR2) RETURN TIMESTAMP WITH TIME ZONE;

SELECT TO_TIMESTAMP_TZ('2004-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')
FROM dual;

Overload 4

TO_TIMESTAMP(right IN TIMESTAMP) RETURN TIMESTAMP WITH TIME ZONE;

TBD

Overload 5

TO_TIMESTAMP(arg IN DATE) RETURN TIMESTAMP WITH TIME ZONE;

TBD

Overload 6

TO_TIMESTAMP(arg IN TIMESTAMP WITH LOCAL TIME ZONE) RETURN TIMESTAMP WITH TIME ZONE;

TBD

TO_YMINTERVAL

Converts a character string to an INTERVAL YEAR TO MONTH type

TO_YMINTERVAL(right IN VARCHAR2) RETURN INTERVAL YEAR TO MONTH;

SELECT hire_date, hire_date+TO_YMINTERVAL('01-02') "14 months" FROM emp;

TRANSLATE USING

Converts char into the character set specified for conversions between the database character set and the national character set

TRANSLATE(char_string USING <CHAR_CS character_set | NCHAR_CS character set>)

conn oe/oe

CREATE TABLE translate_tab (
char_col VARCHAR2(100),
nchar_col NVARCHAR2(50));

desc translate_tab

INSERT INTO translate_tab
SELECT NULL, translated_name
FROM product_descriptions
WHERE product_id = 3501;

col char_col format a30
col nchar_col format a30

SELECT * FROM translate_tab;

UPDATE translate_tab
SET char_col = TRANSLATE(nchar_col USING CHAR_CS);

SELECT * FROM translate_tab;

UNISTR

Convert String To The National Character Set (either UTF8 or UTF16)

UNISTR(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NVARCHAR2;

SELECT UNISTR(ASCIISTR(CHR(128) || 'Hello' || CHR(255)))
FROM dual;

Related Topics
Date Functions
Numeric Functions
String Functions
Timestamp
UTL_RAW
XML Functions

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated: