oracle+ascii转汉字,oracle常用cast、to_char及asciistr等转换函数函数大全

oracle常用cast、to_char及asciistr等转换函数函数大全

发布日期：2015-11-05 14:09

来源：

标签：

数据库

oracle教程

oracle函数

oracle转换函数

本章我们主要学习oracle数据库实际应用中常用转换函数有哪些？下面我们就做一下具体讲解，希望大家多多支持中国站长网络学院。

注：功能转换为日期，数字，字符串，和timestamp数据类型可以通过相关链接发现。

1、ASCIISTR

转换ASCII字符串在数据库中的字符的ASCII字符串集

ASCIISTR(ch VARCHAR2 CHARACTER SET ANY_CS)

RETURN VARCHAR2 CHARACTER SET ch%CHARSET;

eg:

SELECT ASCIISTR(CHR(128) || 'Hello' || CHR(255))

FROM dual;

2、BFILENAME

返回一个组合的目录和文件的名称BFILE

BFILENAME(directory IN VARCHAR2, filename IN VARCHAR2)

RETURN BFILE;

eg:

DECLARE

src_file BFILE;

BEGIN

src_file := bfilename('CTEMP', 'myfile.txt');

END;

/

3、BIN_TO_NUM

将一个位向量转换为一个数字

BIN_TO_NUM(,,....)

eg:SELECT BIN_TO_NUM(1, 0, 1, 0) FROM dual;

4、CAST

将一种内置数据类型或集合类型的值到另一个内置类型或类型的值的集合

CAST( AS )

eg:

SELECT CAST(15402 AS VARCHAR2(30))

FROM dual;

5、CHARTOROWID

将一个值从CHAR，VARCHAR2数据类型，数据类型，或为ROWID数据类型

CHARTOROWID();

eg:

conn hr/hr

SELECT rowid

FROM employees;

SELECT last_name

FROM employees

WHERE ROWID = CHARTOROWID('AAAQvRAAFAAAABYAAp');

6、COMPOSE

在任何数据类型转换到Unicode字符串

COMPOSE()

eg：SELECT 'ol' || COMPOSE('e' || UNISTR('\0301')) FROM dual;

7、CONVERT

将字符串从一个字符集转换为另一个字符集

CONVERT(, , )

eg：

SELECT CONVERT('� � � � � A B C D E','US7ASCII','WE8ISO8859P1')

FROM dual;

8、DECOMPOSE

将一个Unicode字符串转换为字符串

DECOMPOSE()

eg：

SELECT DECOMPOSE('ol' || COMPOSE('e' || UNISTR('\0301')))

FROM dual;

9、HEXTORAW

转换字符，含十六进制数字字符VARCHAR2，NCHAR或NVARCHAR2，字符集的原始值

HEXTORAW()

eg:

CREATE TABLE test (

raw_col RAW(10));

desc test

INSERT INTO test VALUES (HEXTORAW('7D'));

SELECT * FROM test;

10、NUMTODSINTERVAL

将一个数字转换为一个时间间隔的一天为二字

NUMTODSINTERVAL(n, )

eg：

SELECT NUMTODSINTERVAL(120, 'DAY') FROM dual;

SELECT NUMTODSINTERVAL(1200, 'HOUR') FROM dual;

SELECT NUMTODSINTERVAL(12000, 'MINUTE') FROM dual;

SELECT NUMTODSINTERVAL(120000, 'SECOND') FROM dual;

11、NUMTOYMINTERVAL

将N转换为一个间隔年为月的文字

NUMTOYMINTERVAL(n, )

eg：

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;

12、RAWTOHEX

将原来包含它的等效十六进制字符值

RAWTOHEX()

eg：

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;

13、REFTOHEX

转换参数表达式到含有它的等效十六进制字符值。表达式必须返回一个参考文献

REFTOHEX();

eg：

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;

14、ROWIDTOCHAR

将一个rowid值转换成VARCHAR2数据类型

ROWIDTOCHAR(rowid);

eg：

SELECT COUNT(*) FROM servers;

SELECT rowid FROM servers WHERE rownum < 11;

SELECT ROWID FROM servers WHERE ROWIDTOCHAR(ROWID) LIKE '%AAB%';

15、SCN_TO_TIMESTAMP

返回一个SCN的近似时间戳

SCN_TO_TIMESTAMP();

eg：

SELECT current_scn FROM gv$database;

SELECT SCN_TO_TIMESTAMP(8215026-250000) FROM dual;

16、TIMESTAMP_TO_SCN

返回一个时间戳的近似SCN

TIMESTAMP_TO_SCN()

eg：

SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;

17、TO_BINARY_DOUBLE

将值转换为数据类型的binary_double

TO_BINARY_DOUBLE();

eg：

See TO_NUMBER demo, below.

18、TO_BINARY_FLOAT

将值转换为数据类型的binary_float

TO_BINARY_FLOAT() RETURN BINARY_FLOAT

eg：See TO_NUMBER demo, below.

19、TO_CHAR

1)转换数据类型的字符串

TO_CHAR(, ) RETURN VARCHAR2

eg:SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS') FROM dual;

2)将日期和时间戳字段转化为指定的格式

TO_DATE(, , )

eg：

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;

3)转换字符数

TO_CHAR(number)

eg：

SELECT TO_CHAR(123) FROM dual;

4)将数字转换为十六进制

TO_CHAR(NUMBER) RETURN HEX

eg：

SELECT TO_CHAR(1048576,'XXXXXXXX') FROM dual;

20、TO_CLOB

转换字符，nchar，VARCHAR2类型，或者，NCLOB值CLOB值

TO_CLOB() RETURN CLOB

eg：SELECT TO_CLOB('Some value') FROM dual;

21、TO_DATE

1)将一个字符串以默认格式转换为日期

TO_DATE() RETURN DATE

eg:SELECT TO_DATE('01-JAN-2004') FROM dual;

2)将一个字符串以非默认格式转换为日期

TO_DATE(, )

eg:SELECT TO_DATE('01/01/2004', 'MM/DD/YYYY') FROM dual;

3)用非默认格式转换一个字符串并指定语言

TO_DATE(, ) RETURN DATE

eg:

SELECT TO_DATE('January 12, 2005, 11:03 A.M.', 'MONTH DD, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')

FROM dual;

4)转换一个字符串到24小时的时间

TO_DATE(, ) RETURN DATE

eg:SELECT TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS') FROM dual;

22、TO_DSINTERVAL

将字符串转换为时间间隔一天二数据类型

TO_DSINTERVAL(, , )

eg:

conn hr/hr

SELECT employee_id, last_name

FROM employees

WHERE hire_date + TO_DSINTERVAL('100 10:00:00') <= DATE '1990-01-01';

23、TO_MULTI_BYTE

返回字符串的所有单字节字符转换为相应的多字节字符

TO_MULTI_BYTE(character_string)

eg:

-- must be run in a UTF8 database to see the difference

SELECT dump('A') FROM dual;

SELECT dump(TO_MULTI_BYTE('A')) FROM dual;

24、TO_NCHAR

将日期或时间戳从设置到国家字符集指定数据库的字符

TO_NCHAR(,

, ) RETURN NCHAR

eg:

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;

25、TO_NUMBER

1)将字符串转换为数字数据类型

TO_NUMBER([, , ]) RETURN NUMBER

eg:

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;

2)将十六进制数转换为浮动

TO_NUMBER(, );

eg:SELECT TO_NUMBER('0A', 'XX') FROM dual;

3)将十六进制数转换为十进制

TO_NUMBER(,

'') RETURN ;

eg:

SELECT TO_NUMBER(100000,'XXXXXXXX')

FROM dual;

26、TO_SINGLE_BYTE

返回字符串的所有多字节字符转换为相应的单字节字符

TO_SINGLE_BYTE(character_string)

eg:

-- must be run in a UTF8 database to see the difference

SELECT TO_SINGLE_BYTE(CHR(15711393))

FROM dual;

27、TO_TIMESTAMP

将字符串转换为时间戳数据类型

TO_TIMESTAMP(, , ) RETURN TIMESTAMP

eg:SELECT TO_TIMESTAMP('2004-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') FROM dual;

28、UNISTR

字符串转换为国家字符集(或者UTF8或UTF16)

UNISTR(ch VARCHAR2 CHARACTER SET ANY_CS) RETURN NVARCHAR2;

eg:SELECT UNISTR(ASCIISTR(CHR(128) || 'Hello' || CHR(255))) FROM dual;