【转】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. ASCIISTRConverts An ASCII String To An ASCII String In The Database's Character SetASCIISTR(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 nameBFILENAME(directory IN VARCHAR2, filename IN VARCHAR2) RETURN BFILE;DECLARE
src_file BFILE;
BEGIN
src_file := bfilename('CTEMP', 'myfile.txt');
END;
/ BIN_TO_NUMConverts a bit vector to a numberBIN_TO_NUM(<value>,<value>,....)SELECT BIN_TO_NUM(1, 0, 1, 0) FROM dual; CASTConverts one built-in datatype or collection-typed value into another built-in datatype or collection-typed valueCAST(<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 datatypeCHARTOROWID(<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 stringCOMPOSE(<string_or_column>)

Unistring ValueResulting characterunistr('\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';
SymbolDescriptionROUNDTRUNCCCOne greater than the first two digits of a four-digit yearXXSQL> SELECT TO_CHAR(SYSDATE, 'CC') FROM dual;

TO_CHAR(SYSDATE,'CC')
---------------------------------------------
21

DStarting day of the weekXXSQL> SELECT TO_CHAR(SYSDATE, 'D') FROM dual;

TO_CHAR(SYSDATE,'D')
--------------------------------------------
4

DDDayXXSQL> SELECT TO_CHAR(SYSDATE, 'DD') FROM dual;

TO_CHAR(SYSDATE,'DD')
---------------------------------------------
02

DDDDayXXSQL> SELECT TO_CHAR(SYSDATE, 'DDD') FROM dual;

TO_CHAR(SYSDATE,'DDD')
----------------------------------------------
093

DAYStarting day of the week XXSQL> SELECT TO_CHAR(SYSDATE, 'DAY') FROM dual;

TO_CHAR(SYSDATE,'DAY')
----------------------------------------------
WEDNESDAY

DYStarting day of the week XXSQL> SELECT TO_CHAR(SYSDATE, 'DY') FROM dual;

TO_CHAR(SYSDATE,'DY')
----------------------------------------------
WED

HHHours XXSQL> SELECT TO_CHAR(SYSDATE, 'HH') FROM dual;

TO_CHAR(SYSDATE,'HH')
---------------------------------------------
10

HH12Hours SQL> SELECT TO_CHAR(SYSDATE, 'HH12') FROM dual;

TO_CHAR(SYSDATE,'HH12')
-----------------------------------------------
10

HH24Hours SQL> SELECT TO_CHAR(SYSDATE, 'HH24') FROM dual;

TO_CHAR(SYSDATE,'HH24')
-----------------------------------------------
22

IISO YearXXSQL> SELECT TO_CHAR(SYSDATE, 'I') FROM dual;

TO_CHAR(SYSDATE,'I')
--------------------------------------------
8

IWSame 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

IYISO Year SQL> SELECT TO_CHAR(SYSDATE, 'IY') FROM dual;

TO_CHAR(SYSDATE,'IY')
---------------------------------------------
08

IYYISO Year SQL> SELECT TO_CHAR(SYSDATE, 'IYY') FROM dual;

TO_CHAR(SYSDATE,'IYY')
------------------------------------------------
008

IYYYISO Year SQL> SELECT TO_CHAR(SYSDATE, 'IYYY') FROM dual;

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

JJulian Day SQL> SELECT TO_CHAR(SYSDATE, 'J') FROM dual;

TO_CHAR(SYSDATE,'J')
--------------------------------------------
2454559

MIMinutes XXSQL> SELECT TO_CHAR(SYSDATE, 'MI') FROM dual;

TO_CHAR(SYSDATE,'MI')
---------------------------------------------
29

MMMonth (rounds up on the sixteenth day) SQL> SELECT TO_CHAR(SYSDATE, 'MM') FROM dual;

TO_CHAR(SYSDATE,'MM')
---------------------------------------------
04

MONMonth (rounds up on the sixteenth day) XXSQL> SELECT TO_CHAR(SYSDATE, 'MON') FROM dual;

TO_CHAR(SYSDATE,'MON')
----------------------------------------------
APR

MONTHMonth (rounds up on the sixteenth day) XXSQL> SELECT TO_CHAR(SYSDATE, 'MONTH') FROM dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------------------
APRIL

QQuarter (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

RMMonth (rounds up on the sixteenth day) in Roman Numerals SQL> SELECT TO_CHAR(SYSDATE, 'RM') FROM dual;

TO_CHAR(SYSDATE,'RM')
---------------------------------------------
IV

SCCOne greater than the first two digits of a four-digit yearXXSQL> SELECT TO_CHAR(SYSDATE, 'SCC') FROM dual;

TO_CHAR(SYSDATE,'SCC')
----------------------------------------------
21

SYYYYYear (rounds up on July 1)XXSQL> SELECT TO_CHAR(SYSDATE, 'SYYYY') FROM dual;

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

WWeek number in the month SQL> SELECT TO_CHAR(SYSDATE, 'W') FROM dual;

TO_CHAR(SYSDATE,'W')
--------------------------------------------
1

WWWeek of the year SQL> SELECT TO_CHAR(SYSDATE, 'WW') FROM dual;

TO_CHAR(SYSDATE,'WW')
---------------------------------------------
14

YOne Digit YearXXSQL> SELECT TO_CHAR(SYSDATE, 'Y') FROM dual;

TO_CHAR(SYSDATE,'Y')
--------------------------------------------
8

YYTwo Digit YearXXSQL> SELECT TO_CHAR(SYSDATE, 'YY') FROM dual;

TO_CHAR(SYSDATE,'YY')
---------------------------------------------
08

YYYThree Digit YearXXSQL> SELECT TO_CHAR(SYSDATE, 'YYY') FROM dual;

TO_CHAR(SYSDATE,'YYY')
----------------------------------------------
008

YYYYFour Digit YearXXSQL> 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阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 网际浪人