Oracle Conversion Functions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose This page is a collection of built-in Oracle Database functions used to convert data types.

Additional undocumented string related functions can be found on the Undocumented Oracle page also linked at page bottom.
Note
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
Dependencies
STANDARD    
 
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@pdborcl

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;
conn uwclass/uwclass@pdbdev

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@pdborcl

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 linked at page bottom
RAWTOHEX(r IN RAW) RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

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;
conn uwclass/uwclass@pdbdev

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@pdborcl

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;
conn uwclass/uwclass@pdbdev

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

Source dbmstran.sql
SCN_TO_TIMESTAMP(query_scn IN NUMBER) RETURN TIMESTAMP
IS EXTERNAL NAME "ktfexscntot"
WITH CONTEXT
PARAMETERS(context, query_scn OCINUMBER, RETURN)
LIBRARY DBMS_TRAN_LIB;
/
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

Source dbmstran.sql
TIMESTAMP_TO_SCN(query_time IN TIMESTAMP) RETURN NUMBER
IS EXTERNAL NAME "ktfexttoscn"
WITH CONTEXT
PARAMETERS(context, query_time OCIDATETIME, RETURN)
LIBRARY DBMS_TRAN_LIB;
/
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
conn uwclass/uwclass@pdbdev

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-2014'), '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               T
--------------------  -
17-FEB-2014 22:12:13  2
DD Day X X
SELECT sysdate, TO_CHAR(sysdate, 'DD') FROM dual;

sysdate               TO
--------------------  --
17-FEB-2014 22:12:34  17
DDD Day X X
SELECT TO_CHAR(sysdate, 'DDD') FROM dual;

TO_CHAR(sysdate,'DDD')
----------------------------------------------
048
DAY Starting day of the week X X
SELECT sysdate, TO_CHAR(sysdate, 'DAY') FROM dual;

sysdate              TO_CHAR(S
-------------------- ---------
17-FEB-2014 10:28:09 MONDAY
DL Day and long date format X  
SELECT TO_CHAR(sysdate, 'DL') FROM dual;

TO_CHAR(sysdate,'DL')
-----------------------------
Monday, February 17, 2014
DS Short date format  X  
SELECT TO_CHAR(sysdagte, 'DS') FROM dual;

TO_CHAR(SY
----------
2/17/2014
DY Current day of the week X X
SELECT sysdate, TO_CHAR(sysdate, 'DY') FROM dual;

sysdate               TO_
--------------------  ---
17-FEB-2014 20:53:31  MON
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,
-----------------------------
Monday , February    17, 2014

SELECT TO_CHAR(sysdate, 'FMDay, Month DD, YYYY') FROM dual;

TO_CHAR(sysdate,'FMDAY,MONTHD
-----------------------------
Monday, February 17, 2014

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,
-----------------------------
Monday, February    17, 2014

SELECT TO_CHAR(sysdate, 'FXDay, Month DD, YYYY') FROM dual;

TO_CHAR(SYSDATE,'FMDAY,MONTHD
-----------------------------
Monday   , February 17, 2014

HH
Truncate Date-Time to Hours
X

X
SELECT sysdate, TO_CHAR(sysdate, 'HH') FROM dual;

SYSDATE              TO
-------------------- --
17-FEB-2014 11:01:03 11

SELECT sysdate, TRUNC(sysdate, 'HH') FROM dual;

SYSDATE              TRUNC(SYSDATE,'HH')
-------------------- --------------------
17-FEB-2014 11:01:21 17-FEB-2014 11:00:00
HH12 Hours (12 hour clock) X  
SELECT sysdate, TO_CHAR(sysdate, 'HH12') FROM dual;

SYSDATE              TO
-------------------- --
17-FEB-2014 11:02:30 11
HH24 Hours (24 hour clock) X  
SELECT sysdate, TO_CHAR(sysdate+5/24, 'HH24') FROM dual;

SYSDATE              TO
-------------------- --
17-FEB-2014 11:03:44 16
I ISO Year X X
SELECT sysdate, TO_CHAR(sysdate, 'I') FROM dual;

SYSDATE               T
--------------------  -
17-FEB-2014 21:36:37  4
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
--------------------  --
17-FEB-2014 22:08:57  08
IY ISO Year X X
SELECT sysdate, TO_CHAR(sysdate, 'IY') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2014 22:10:04  14
IYY ISO 3 Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'IYY') FROM dual;

SYSDATE               TO_
--------------------  ---
17-FEB-2014 20:51:59  014
IYYY ISO Year X X
SELECT sysdate, TO_CHAR(sysdate, 'IYYY') FROM dual;

SYSDATE               TO_C
--------------------  ----
17-FEB-2014 20:51:36  2014
J Julian Day X X
SELECT sysdate, TO_CHAR(sysdate, 'J') FROM dual;

SYSDATE               TO_CHAR
--------------------  -------
17-FEB-2014 11:11:03  2456706
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
--------------------  --
17-FEB-2014 11:11:47  11
MM Month (rounds up on the sixteenth day) X X
SELECT sysdate, TO_CHAR(sysdate, 'MM') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2014 11:12:07  02
MON Month abreviation X X
SELECT sysdate, TO_CHAR(sysdate, 'MON') FROM dual;

SYSDATE               TO_
--------------------  ---
07-JAN-2014 20:26:09  JAN
MONTH Month name spelled out X X
SELECT sysdate, TO_CHAR(sysdate, 'MONTH') FROM dual;

SYSDATE               TO_CHAR(S
--------------------  ---------
07-JAN-2014 20:26:38  JANUARY

PM
P.M.
Returns "PM" if the time is after noon: Otherwise "AM"
X
 
SELECT TO_CHAR(TO_DATE('01-JAN-2014') + 3/24, 'AM') FROM dual;

TO
--
AM

SELECT TO_CHAR(TO_DATE('01-JAN-2014') + 13/24, 'PM') FROM dual;

TO
--
PM

Q
Quarter
X

X
SELECT sysdate, TO_CHAR(sysdate, 'Q') FROM dual;

SYSDATE               T
--------------------  -
17-FEB-2014 11:14:08  1

SELECT sysdate, TRUNC(sysdate, 'Q') FROM dual;

SYSDATE               TRUNC(SYSDATE,'Q')
--------------------  --------------------
17-FEB-2014 11:14:32  01-JAN-2014 00:00:00
RM Roman Numeral Month X X
SELECT sysdate, TO_CHAR(sysdate, 'RM') FROM dual;

SYSDATE               TO_C
--------------------  ----
17-FEB-2014 11:15:10  II
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
--------------------  --
17-FEB-2014 11:15:31  14
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-JAN-2014 20:54:45  2014
SCC Century (same as CC) X X
SELECT sysdate, TO_CHAR(sysdate, 'SCC') FROM dual;

SYSDATE               TO_
--------------------  ---
17-FEB-2014 11:20:09  21

SP
Spelled out
X
 
SELECT sysdate, TO_CHAR(sysdate, 'SP') FROM dual;

SQL> 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')
-------------------- ----------------------------------
17-FEB-2014 11:21:26 TWO

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'DDSP') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'DDSP')
-------------------- ----------------------------------
17-FEB-2014 11:22:54 SEVENTEEN

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'YYSP') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'YYSP')
-------------------- ----------------------------------
17-FEB-2014 11:22:28 FOURTEEN

SPTH
Spelled out in ordinal format
X
 
SELECT sysdate, TO_CHAR(sysdate, 'SPTH') FROM dual;

SQL> SELECT sysdate, TO_CHAR(sysdate, 'SPTH') 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')
-------------------- ----------------------------------
17-FEB-2014 11:23:53 SECOND

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'DDSPTH') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'DDSPTH')
-------------------- ----------------------------------
17-FEB-2014 11:24:20 SEVENTEENTH

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'YYSPTH') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'YYSPTH')
-------------------- ----------------------------------
17-FEB-2014 11:24:49 FOURTEENTH
SS Seconds X  
SELECT sysdate, TO_CHAR(sysdate, 'SS') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2014 11:25:30  30
SSSSS Seconds past midnight X  
SELECT sysdate, TO_CHAR(sysdate, 'SSSSS') FROM dual;

SYSDATE               TO_CH
--------------------  -----
17-FEB-2014 11:25:54  41154
SYEAR Year spelled out X X
SELECT sysdate, TO_CHAR(sysdate, 'SYEAR') FROM dual;

SYSDATE               TO_CHAR(SYSDATE,'SYEAR')
--------------------  ------------------------
07-JAN-2014 20:48:10  TWENTY FOURTEEN
SYYYY Four digit year. Prefixes BC with a minus sign X X
SELECT sysdate, TO_CHAR(sysdate, 'SYYYY') FROM dual;

SYSDATE               TO_CH
--------------------  -----
07-JAN-2014 21:15:33  2014
TS Short time format X  
SELECT sysdate, TO_CHAR(sysdate, 'TS') FROM dual;

SYSDATE               TO_CHAR(SYS
--------------------  -----------
07-JAN-2014 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
--------------------  -
17-FEB-2014 11:35:37  3
WW Week of the year X X
SELECT sysdate, TO_CHAR(sysdate, 'WW') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2014 11:36:03  07
X Local Radix Character X  
SELECT TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MM:SSXFF') FROM dual;
                                  *
TO_CHAR(SYSTIMESTAMP,'DD-MON-Y
------------------------------
17-FEB-2014 11:02:38.677000
Y One Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'Y') FROM dual;

SYSDATE               T
--------------------  -
17-FEB-2014 11:36:51  4
YEAR Year name spelled out X X
SELECT sysdate, TO_CHAR(sysdate, 'YEAR') FROM dual;

SYSDATE              TO_CHAR(SYSDATE,'YEAR')
-------------------- -----------------------
17-FEB-2014 07:01:42 TWENTY FOURTEEN
YY Two Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'YY') FROM dual;

SYSDATE               TO
--------------------  --
07-JAN-2014 21:04:20  14
YYY Three Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'YYY') FROM dual;

SYSDATE               TO_
--------------------  ---
07-JAN-2014 21:04:32  014
YYYY Four Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'YYYY') FROM dual;

SYSDATE               TO_C
--------------------  ----
07-JAN-2014 21:04:42  2014

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;

then TKyte demo
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 string value')
FROM dual;
Overload 2 TO_CLOB(cl IN CLOB CHARACTER SET ANY_CS) RETURN CLOB;
SELECT TO_CLOB('Some string value')
FROM dual;
 
TO_DATE
Overload 1Convert 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-2014') 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/2014', 'MM/DD/YYYY') FROM dual;
Convert A String With A Non-Default Format And Specify The Language
Overload 2
SELECT TO_DATE('January 12, 2014, 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;
conn uwclass/uwclass@pdbdev

ALTER SESSION SET NLS_TERRITORY = 'JAPAN';

SELECT TO_DATE('January 12, 2014, 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@pdborcl

SELECT employee_id, last_name
FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00') <= DATE '2006-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@pdborcl

SELECT employee_id, last_name
FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00') <= DATE '2005-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@pdborcl

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@pdborcl

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;
conn uwclass/uwclass@pdbdev

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-2149 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('2014-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-JAN-2014 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
------------------------------------ ---------
17-FEB-14 12.52.51.206000 PM -08:00  2

SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF3') FROM dual;

SYSTIMESTAMP                         TO_CHAR(S
------------------------------------ ---------
17-FEB-14 12.53.14.936000 PM -08:00  936

SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF5') FROM dual;

SYSTIMESTAMP                         TO_CHAR(S
------------------------------------ ---------
17-FEB-14 12.53.32.539000 PM -08:00  53900

SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF7') FROM dual;

SYSTIMESTAMP                         TO_CHAR(S
------------------------------------ ---------
17-FEB-14 12.53.48.417000 PM -08:00  4170000

TZD
Timestamp with daylight savings time
X
 
SELECT TO_CHAR(TO_TIMESTAMP_TZ('2014-02-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('2014-02-1513:27:58CET','YYYY-MM-DD
-----------------------------------------------------------
2014-02-15 13:27:58 CET CET

SELECT TO_TIMESTAMP_TZ('2/15/2014 7:29 AM PDT','MM/DD/YYYY HH:MI AM TZD') FROM dual;

TO_TIMESTAMP_TZ('2/15/20147:29AMPDT','MM/DD/YYYYHH:MIAMTZD')
------------------------------------------------------------
15-FEB-14 07.29.00.000000000 AM -08:00
TZH Time zone hour X  
SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZH') FROM dual;

SYSTIMESTAMP                         TO_
-----------------------------------  ---
17-FEB-14 12.55.34.739000 PM -08:00  -08
TZM Time zone minute X  
SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZM') FROM dual;

SYSTIMESTAMP                         TO
-----------------------------------  --
17-FEB-14 12.55.49.150000 PM -08:00  00
TZR Time zone region X  
SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZR') FROM dual;

SYSTIMESTAMP                         TO_CHAR(SYSTIMESTAMP,'TZR')
-----------------------------------  ------------------------
17-FEB-14 12.56.18.389000 PM -08:00  -08:00

SELECT TO_CHAR( TO_TIMESTAMP_TZ('2014-02-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
--------------------------
2014-02-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('2012-01-12 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')
FROM dual;

SELECT TO_TIMESTAMP_TZ('2012-04-19T11:50:19-04:00','YYYY-MM-DD"T"HH24: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@pdborcl

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
Analytic Functions
Built-in Functions
CASE
CAST
Character Set Functions
Collection Functions
Data Mining Functions
Date Functions
Miscellaneous Functions
Numeric Functions
OLAP Functions
Operators (Built-in)
Regular Expressions
REPLACE
String Functions
SUBSTRING
Timestamp Functions
TRANSLATE
Undocumented Oracle
UTL_RAW
XML Functions

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved