ITPub博客

首页 > 数据库 > Oracle > [整理]oracle中几个特殊的函数

[整理]oracle中几个特殊的函数

原创 Oracle 作者:halfgene 时间:2007-08-27 15:08:53 0 删除 编辑

1、全角转半角函数 TO_SINGLE_BYTE
2、数字转英文,利用to_char、to_date
3、sys_guid()

[@more@]

1、全角转半角函数 TO_SINGLE_BYTE

SQL> select TO_SINGLE_BYTE('oracle') from dual;

TO_SINGLE_BYTE('ORACLE')
------------------------------
oracle

2、数字转英文,利用to_char、to_date

SQL> select to_char(to_date('12345','J'),'Jsp') en from dual;

en
----------------------------------------
Twelve Thousand Three Hundred Forty-Five

不过有限制:一是长度的限制,二是不能转换带小数的

SQL> select to_char(to_date('88888882345','J'),'Jsp') from dual;

select to_char(to_date('88888882345','J'),'Jsp') from dual

ORA-01854: julian 日期必须介于 1 和 5373484 之间

julian date指的是公元前4712年1月1日起经过的天数.

the inner TO_CHAR simply converts the number (which would probably be a numeric variable in practice) to CHAR so some magic can happen ...

the TO_DATE converts the CHAR using the J (Julian day) format. (the Julian day is the number of days since January 1, 4712BC, which is when SQL*Plus was invented),

having established the date value, we then convert that date back to a Julian day. Because the TO_CHAR in this case is used in DATE context, we can use the J mask to duplicate the original value, and append the SP (spell) format mask. 'Spell" does exactly that - it converts the number to words, hence the string value above.
SP can be used in a number of situations. For example, if SYSDATE is 26-AUG-98, then :

SELECT TO_CHAR ( SYSDATE, 'DdSp') FROM dual; -- spells the day as Twenty-Six,
and
SELECT TO_CHAR ( SYSDATE, 'DDSPTH') FROM dual; --returns TWENTY-SIXTH

3、sys_guid()

Oracle里有一个SYS_ID函数,可以产生GUID,但是返回的是32字节的字符串,但是如果用字符串作为主键的话效率不高

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

请登录后发表评论 登录
全部评论
  • 博文量
    44
  • 访问量
    52962