【转】Oracle Conversion Functions

博客园 - 网际浪人

.Net Framework 4.0 中利用Task实现并行处理、串并行混合处理 C# Process调用应用程序失败时应注意的问题程序员的幽默 To腾讯：强行收集用户个人隐私的行为不可饶恕 VS2005打开VS2008项目的2种方法（转）晨星、银河基金业绩排行榜数据转换工具 ORACLE纯SQL实现多行合并一行 ASP.NET项目添加Log4Net后，发布后无法写日志 “必应”不应、“谷歌”不歌你好2009，再见2008，牛年犇犇犇一种在SQLServer中实现Sequence的高效方法 oledb使用Access更新和插入操作的注意点 5.12大地震——小学二年级小表妹谢可欣的诗 GridView自动排序 [转]SQL Server 2005链接字符串对HtmlEncode的增强——HtmlEntitiesEncode GridView中使用DataKeyNames存储数据键值 C#调用Excel VBA宏封装SoapException处理Webservice异常

网际浪人 · 2010-03-26 · via 博客园 - 网际浪人

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;

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 网际浪人