| 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('\0301')) 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('\0301')))
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 |
| 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 an 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 an 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; |
