PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle Conversion Functions
Version 11.1
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 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>,....)
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(<char>);
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(<string_or_column>)
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('1')) FROM DUAL;
 
CONVERT
Converts a character string from one character set to another CONVERT(<char>, <dest_char_set>, <source_char_set>)
SELECT CONVERT('Ä Ê Í Ó Ø A B C D E','US7ASCII','WE8ISO8859P1')
FROM DUAL;
 
DECOMPOSE
Converts a unicode string to a string DECOMPOSE(<unicode_string>)
SELECT DECOMPOSE('ol' || COMPOSE('e' || UNISTR('1')))
FROM DUAL;
 
HEXTORAW

Converts char containing hexadecimal digits in the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 character set to a raw value		 HEXTORAW(<hex_value>)
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(n, <interval_unit>)
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(n, <interval_unit>)
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		 RAWTOHEX(<raw_value>)
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 RAWTONHEX(<raw_value>);
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(rowid);
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 ROWIDTOCHAR(rowid)
See ROWIDTOCHAR demo above
 
SCN_TO_TIMESTAMP

Returns the approximate Timestamp for an SCN		 SCN_TO_TIMESTAMP(<scn>);
SELECT current_scn
FROM v$database;

SELECT SCN_TO_TIMESTAMP(8215026-250000)
FROM DUAL;
 
TIMESTAMP_TO_SCN
Returns the approximate SCN for a timestamp TIMESTAMP_TO_SCN(<timestamp>)
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP)
FROM DUAL;
 
TO_BINARY_DOUBLE
Converts a Value to the BINARY_DOUBLE Data Type TO_BINARY_DOUBLE(<value>);
See TO_NUMBER demo, below.
 
TO_BINARY_FLOAT
Converts a Value to the BINARY_FLOAT Data Type TO_BINARY_FLOAT(<value>) RETURN BINARY_FLOAT
See TO_NUMBER demo, below.
 
TO_CHAR
Convert Datatype To String TO_CHAR(<string_or_column>, <format>) RETURN VARCHAR2
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS') FROM DUAL;

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

 TO_DATE(<date_string>, <format mask>, <NLS_PARAMETER>)
-- before running these demos
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
 
Symbol Description ROUND TRUNC
CC One greater than the first two digits of a four-digit year X X
SQL> SELECT TO_CHAR(SYSDATE, 'CC') FROM DUAL;

TO_CHAR(SYSDATE,'CC')
---------------------------------------------
21
D Starting day of the week X X
SQL> SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL;

TO_CHAR(SYSDATE,'D')
--------------------------------------------
4
DD Day X X
SQL> SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL;

TO_CHAR(SYSDATE,'DD')
---------------------------------------------
02
DDD Day X X
SQL> SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL;

TO_CHAR(SYSDATE,'DDD')
----------------------------------------------
093
DAY Starting day of the week  X X
SQL> SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;

TO_CHAR(SYSDATE,'DAY')
----------------------------------------------
WEDNESDAY
DY Starting day of the week  X X
SQL> SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL;

TO_CHAR(SYSDATE,'DY')
----------------------------------------------
WED
HH Hours  X X
SQL> SELECT TO_CHAR(SYSDATE, 'HH') FROM DUAL;

TO_CHAR(SYSDATE,'HH')
---------------------------------------------
10
HH12 Hours    
SQL> SELECT TO_CHAR(SYSDATE, 'HH12') FROM DUAL;

TO_CHAR(SYSDATE,'HH12')
-----------------------------------------------
10
HH24 Hours    
SQL> SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL;

TO_CHAR(SYSDATE,'HH24')
-----------------------------------------------
22
I ISO Year X X
SQL> SELECT TO_CHAR(SYSDATE, 'I') FROM DUAL;

TO_CHAR(SYSDATE,'I')
--------------------------------------------
8
IW Same day of the week as the first day of the ISO year    
SQL> SELECT TO_CHAR(SYSDATE, 'IW') FROM DUAL;

TO_CHAR(SYSDATE,'IW')
---------------------------------------------
14
IY ISO Year    
SQL> SELECT TO_CHAR(SYSDATE, 'IY') FROM DUAL;

TO_CHAR(SYSDATE,'IY')
---------------------------------------------
08
IYY ISO Year    
SQL> SELECT TO_CHAR(SYSDATE, 'IYY') FROM DUAL;

TO_CHAR(SYSDATE,'IYY')
------------------------------------------------
008
IYYY ISO Year    
SQL> SELECT TO_CHAR(SYSDATE, 'IYYY') FROM DUAL;

TO_CHAR(SYSDATE,'IYYY')
------------------------------------------------
2008
J Julian Day    
SQL> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;

TO_CHAR(SYSDATE,'J')
--------------------------------------------
2454559
MI Minutes  X X
SQL> SELECT TO_CHAR(SYSDATE, 'MI') FROM DUAL;

TO_CHAR(SYSDATE,'MI')
---------------------------------------------
29
MM Month (rounds up on the sixteenth day)    
SQL> SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL;

TO_CHAR(SYSDATE,'MM')
---------------------------------------------
04
MON Month (rounds up on the sixteenth day)  X X
SQL> SELECT TO_CHAR(SYSDATE, 'MON') FROM DUAL;

TO_CHAR(SYSDATE,'MON')
----------------------------------------------
APR
MONTH Month (rounds up on the sixteenth day)  X X
SQL> SELECT TO_CHAR(SYSDATE, 'MONTH') FROM DUAL;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------------------
APRIL
Q Quarter (rounds up on 16th day of the 2nd month of the quarter)    
SQL> SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;

TO_CHAR(SYSDATE,'Q')
--------------------------------------------
2
RM Month (rounds up on the sixteenth day) in Roman Numerals    
SQL> SELECT TO_CHAR(SYSDATE, 'RM') FROM DUAL;

TO_CHAR(SYSDATE,'RM')
---------------------------------------------
IV
SCC One greater than the first two digits of a four-digit year X X
SQL> SELECT TO_CHAR(SYSDATE, 'SCC') FROM DUAL;

TO_CHAR(SYSDATE,'SCC')
----------------------------------------------
21
SYYYY Year (rounds up on July 1) X X
SQL> SELECT TO_CHAR(SYSDATE, 'SYYYY') FROM DUAL;

TO_CHAR(SYSDATE,'SYYYY')
------------------------------------------------
2008
W Week number in the month    
SQL> SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL;

TO_CHAR(SYSDATE,'W')
--------------------------------------------
1
WW Week of the year    
SQL> SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL;

TO_CHAR(SYSDATE,'WW')
---------------------------------------------
14
Y One Digit Year X X
SQL> SELECT TO_CHAR(SYSDATE, 'Y') FROM DUAL;

TO_CHAR(SYSDATE,'Y')
--------------------------------------------
8
YY Two Digit Year X X
SQL> SELECT TO_CHAR(SYSDATE, 'YY') FROM DUAL;

TO_CHAR(SYSDATE,'YY')
---------------------------------------------
08
YYY Three Digit Year X X
SQL> SELECT TO_CHAR(SYSDATE, 'YYY') FROM DUAL;

TO_CHAR(SYSDATE,'YYY')
----------------------------------------------
008
YYYY Four Digit Year X X
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;

TO_CHAR(SYSDATE,'YYYY')
-----------------------------------------------
2008

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);
Convert NUMBER to CHARACTER TO_CHAR(number)
SELECT TO_CHAR(123)
FROM DUAL;
Convert NUMBER to HEX TO_CHAR(NUMBER) RETURN HEX
SELECT TO_CHAR(1048576,'XXXXXXXX')
FROM DUAL;
 
TO_CLOB
Converts CHAR, NCHAR, VARCHAR2, NVARCHAR2, or  NCLOB values to CLOB values TO_CLOB(right VARCHAR2 CHARACTER SET ANY_CS) RETURN CLOB;
SELECT TO_CLOB('Some value')
FROM DUAL;
 
TO_DATE   (You may also want to look at this page for for additional information on TO_DATE.
Convert A String With Default Format To A Date TO_DATE(<string>) RETURN DATE
SELECT TO_DATE('01-JAN-2004') FROM DUAL;
Convert A String With A Non-Default Format To A Date TO_DATE(<string>, <format mask>)
SELECT TO_DATE('01/01/2004', 'MM/DD/YYYY') FROM DUAL;
Convert A String With A Non-Default Format And Specify The Language TO_DATE(<string>, <format mask>) RETURN DATE
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 With A Non-Default Format And Specify The Language		 TO_DATE(<date_string>, <format mask>, <NLS_PARAMETER>) 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';
Convert A String To 24 Hour Time TO_DATE(<date_string>, <format mask>) RETURN DATE
SELECT TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS') FROM DUAL;
 
TO_DSINTERVAL

Converts A String To An INTERVAL DAY TO SECOND DataType		 TO_DSINTERVAL(<date_string>, <format mask>, <NLS_PARAMETER>)
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(character_string)
-- 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

Converts a DATE or  TIMESTAMP from the database character set to the National Character Set specified		 TO_NCHAR(<date_string | interval | CLOB | number>,
<format mask>, <NLS_PARAMETER>) RETURN NCHAR
SELECT TO_NCHAR('ABC')
FROM DUAL;

SELECT TO_NCHAR(1048576)
FROM DUAL;

conn oe/oe

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

Converts CLOB values in a LOB column or other character strings to NCLOB		 TO_NCLOB(lob_or_character_value) 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 string to the NUMBER data type		 TO_NUMBER(<value>[, <format>, <NLS parameter>]) 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 TO_NUMBER(<value>, <format>);
SELECT TO_NUMBER('0A', 'XX')
FROM DUAL;

SELECT TO_NUMBER('1F', 'XX')
FROM DUAL;
Converts a HEX number to DECIMAL TO_NUMBER(<binary_float | binary_double | number>,
'<hex mask>') RETURN <binary_float | binary_double | number>;
SELECT TO_NUMBER(100000,'XXXXXXXX')
FROM DUAL;
 
TO_SINGLE_BYTE
Returns char with all of its multibyte characters converted to their corresponding single-byte characters TO_SINGLE_BYTE(character_string)
-- must be run in a UTF8 database to see the difference

SELECT TO_SINGLE_BYTE(CHR(15711393))
FROM DUAL;
 
TO_TIMESTAMP
Converts a string to a Timestamp Data Type TO_TIMESTAMP(<date_string>, <format mask>, <NLS_PARAMETER>)
RETURN TIMESTAMP
SELECT TO_TIMESTAMP('2004-12-01 11:00:00','YYYY-MM-DD HH:MI:SS')
FROM DUAL;
 
TO_TIMESTAMP_TZ
Converts a string to a Timestamp with Timezone Data Type TO_TIMESTAMP(<date_string>, <format mask>, <NLS_PARAMETER>)
RETURN TIMESTAMP WITH TIMEZONE
SELECT TO_TIMESTAMP_TZ('2004-12-01 11:00:00 -8:00',
'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
 
TO_YMINTERVAL
Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type TO_YMINTERVAL(<char>) RETURN YMINTERVAL
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 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
XML Functions
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----