格式转换函数cast与convert

1.cast和convert可以实现什么功能

    在sqlserver中，CAST、CONVERT都可以执行数据类型转换。但是在oracle数据库中，convert一般用于转换字符集。

 

2.convert 转换字符集

语法：

CONVERT(char, dest_char_set [,source_char_set] )

 

示列：

----首先确定本数据库中支持的字符集

SQL> select * from v$nls_valid_values where parameter='CHARACTERSET';

---- convert() 转换字符集

SQL> SELECT CONVERT('? ? ? ? ? A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;

CONVERT('?????ABCDE'

-------------------------

?? ?? ?? ?? ?? A B C D E

 

 

SQL> SELECT CONVERT('? ? ? ? ? A B C D E ', 'US7ASCII') FROM DUAL;

CONVERT('?????A

--------------------

? ? ? ? ? A B C D E

 

----再比如：

SQL> select convert('中国','US7ASCII') from dual;

CON

---

???

SQL> select convert('中国','ZHS16GBK') from dual;

CONVER

------

中国

SQL> select convert('中国','ZHS32GB18030') from dual;

CONVER

------

中国

SQL> select convert('中國','ZHS16GBK') from dual;

CONVER

------

中國

 

 

 

 

　　语法：

 

　　   ---将expr转为data_type类型

 

3.cast数据类型转换

语法：

CAST (expression AS data_type [ (length ) ])    ----将expr转为data_type类型

 

----将12.7转为varchar型

SQL> select cast(12.7 as varchar2(10)) from dual;

CAST(12.7A

----------

12.7

 

----将12.7转为int型

SQL> select cast(12.7 as int) from dual;

CAST(12.7ASINT)

---------------

             13

 

----将12.7转为number(10,-1)型

SQL> select cast(12.7 as number(10,-1)) from dual;

CAST(12.7ASNUMBER(10,-1))

-------------------------

                       10

 

----将当前日期转为varchar型

SQL> select cast(sysdate as varchar2(100)) from dual;

CAST(SYSDATEASVARCHAR2(100))

--------------------------------------------------------------------------------

2016-07-19 07:12:54

 

----将字符串转为日期型

SQL> select cast('2016-07-19 07:22:45' as date) from dual;

CAST('2016-07-1907:

-------------------

2016-07-19 07:22:45

 

 

----案例：

SQL> create table tets(d varchar2(100),name varchar2(100));

Table created.

SQL> insert into tets values('2016-07-11 07:22:45','day1');

SQL> insert into tets values('2016-07-12 07:22:45','day2');

SQL> insert into tets values('2016-07-13 07:22:45','day3');

 

----查询星期三的数据

SQL> select d,to_char(cast(d as date),'day') from tets;

D                              TO_CHAR(CAST(

------------------------------ -------------

2016-07-11 07:22:45            monday

2016-07-12 07:22:45            tuesday

2016-07-13 07:22:45            wednesday

 

SQL> select * from tets where to_char(cast(d as date),'day')='wednesday';

D                              NAME

------------------------------ --------------------------------------------------

2016-07-13 07:22:45            day3

 

----查询周一和周三的数据

SQL> select * from tets where to_char(cast(d as date),'day') in ('wednesday','monday');

D                              NAME

------------------------------ --------------------------------------------------

2016-07-13 07:22:45            day3

 

SQL> select d,to_char(cast(d as date),'day'),length(to_char(cast(d as date),'day')) from tets;

D               TO_CHAR(CAST(       LENGTH(TO_CHAR(CAST(DASDATE),'DAY'))

------------------------------ ------------- ------------------------------------

2016-07-11 07:22:45      monday                                           9

2016-07-12 07:22:45       tuesday                                           9

2016-07-13 07:22:45        wednesday                                        9

 

SQL> select * from tets where to_char(cast(d as date),'day') in ('wednesday','monday   ');

D                              NAME

------------------------------ --------------------------------------------------

2016-07-11 07:22:45            day1

2016-07-13 07:22:45            day3

 

 

 

 

 

 

 

reference     cast     http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions023.htm#SQLRF00613

convert    http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions034.htm#SQLRF00620

 

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2123004/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2123004/