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

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

[@more@]

1、全角转半角函数 TO_SINGLE_BYTE

SQL> select TO_SINGLE_BYTE('ｏｒａｃｌｅ') from dual;

TO_SINGLE_BYTE('ＯＲＡＣＬＥ')
------------------------------
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/594892/viewspace-966376/