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字节的字符串,但是如果用字符串作为主键的话效率不高