【转】Oracle Conversion Functions

最新推荐文章于 2021-04-04 19:39:14 发布

weixin_33754065

最新推荐文章于 2021-04-04 19:39:14 发布

阅读量162

点赞数

文章标签：数据库

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

Related Topics