|
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(<expression>); |
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 gv$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 |
TO_DATE(<date_string>, <format mask>, <NLS_PARAMETER>) |
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
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(<char_or_lob_column_or_value>) 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 specificed |
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; |
| 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; |