SQL语言之转换函数

时间：2010-08-08 来源：jcbut2008

函数转换可以分为两种：隐式数据类型转换和显式数据类型转换

N2C/D2C：字符字段非常灵活，几乎允许存储所有类型的信息。因此，可以很方便地针DATA和NUMBER值转换为它们的字符形式。即实现数字到字符(Number to Character)和日期到字符(Date to Character)的转换。

C2N：只有字符数据表示有效数据时，才可实现字符到数据(Character to NUMBER)的隐式转换。
C2D：当字符串符合：[D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY] separator3 [HH:MI:SS]格式(separator1、separator2、separator3可以是大多数标点符号、空格和制表符)时，才可以实现字符到日期(Character to Date)的隐式转换。

显式数据类型转换(使用函数将数据类型进行转换)
1. TO_CHAR，实现数字、日期到字符的转换。
2. TO_DATE，实现数字、字符到日期的转换。
1. TO_NUM，实现字符、日期到数字的转换。

使用TO_CHAR实现数字到字符的转换
- 语法：TO_CHAR(number1,[format],[nls_parameter])
- format参数必须包含在单引号内
- 数字格式掩码：

格式元素	格式说明	格式	数字	字符结果	备注
9	数字宽度	9999	12	12	最大宽度为4位*
0	显示前面的零 (数字宽度，或代替9)	09999(00000)	0012	00012	最大宽度为5位，不足位用0补足
.	小数点的位置	09999.999	030.40	0030.400
D(Dot)	小数分隔符的位置（默认为句点）	09999D999	030.40	0030.400
,	逗号的位置	09999,999	03040	00003,040
G(Group)	组分隔符的位置（默认为逗号）	09999G999	03040	00003,040
$	美元符号	$099999	03040	$003040
L(Local)	当地货币	L099999	03040	RMB003040	需要将nls_currency值改为RMB，系统默认值为$**
MI	表示负数的减号的位置	99999MI	-3040	3040-	MI只能位于末尾
PR	包围在括号内的负数	99999PR	-3040	<3040>	PR只能位于末尾
EEEE	科学计数法	99.99999EEEE	3040.55	3.04055E+03
U	Nls_dual_currency	U0999999	03040	CAD003040	如果将nls_dual_currency设置为CAD
V	乘以10n次 (n是V之后9的个数)	9999V99	3040	304000
S(symbol)	前面加上+或者-	S999999	3040	+3040

*当需要显示的数字位数大于掩码时，返回哈希符字符串，如select to_char(12345,'099') from dual;系统返回值为：####

**方法为：alter session set nls_currency='RMB'

nls_parameters使用方法：

select to_char(12345,'099999','nls_currency=''RMB''') from dual;

显示结果为：RMB012345

使用TO_CHAR实现日期到字符的转换
- 语法：TO_CHAR(data1,[format],[nls_parameter])
- data1必须是可以被隐式转换为日期的值，可选的format参数大小写，必须将它包含在单引号内。
- 格式掩码会自动给日和月的名称添加空格(默认长度为9)，因此有时需要使用填充模式(fm)运算符将空格去除。

示例：

SQL> select to_char(sysdate,'Month')||'is a special day!' AS "NO fm" from dual;

NO fm

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

May is a special day!

SQL> select SYSDATE,to_char(sysdate,'Month') AS "MONTH",length(to_char(sysdate,'Month')) as "LENGTH" from dual;

SYSDATE MONTH LENGTH

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

05-MAY-10 22:29:47 May 9

使用TO_DATE函数将数字转换为日期
- 语法：TO_DATE(String,[format],[nls_parameter])
- 几乎总是使用可选的format参数，在单引号内指定它。格式掩码与上表中的日期格式掩码相同。
- TO_DATE函数有fx修饰符，其作用为指定string和格式掩码完全匹配。

使用TO_NUMBER函数将字符转换为数字
- 语法：TO_NUMBER(String,[format],[nls_parameter])
- 转换为数字的字符串必须有合适的格式，其格式掩码与上表中数字格式掩码相同。
- 示例： SELECT TO_NUMBER('$1,000.55','$999,999.99') FROM DUAL;返回值为1,000.55。