参考<<OracleDatabase SQL Reference>>.
关于nls可有取值,请注意视图sys.V_$NLS_VALID_VALUES
一,TO_CHAR(NUMBER)
1.1 格式图和简单说明
本函数把参数N转为一个VARCHAR2类型的数值。N可以是NUMBER,BINARY_FLOAT,或者BINARY_DOUBLE。如果不带格式,那么函数会把N转换为足以表示N的VARCHAR2字符串。
格式表参考:
序号 | 格式 | 简例 | 说明 |
1 | ,(逗号) | '9999,999' | 逗号,一般以千分位出现,作为分组符号使用.如果需要您也可以当作是十分位,百分位出现,可以出现N次,视乎数字的大小而定. |
2 | .(点号) | '99.99' | 点号,不要念为"句号",句号是个圆圈,点好只能出现在小数点对应的地方.只能出现一次. |
3 | $(美元符号) | '$999.99' | 美元.其实你可以放在任意地方(在10G下) |
4 | 0(零) | '0999.99' | 零.在对应位置返回对应的字符,如果没有则以'0'填充. |
5 | 9 | '999.99' | 9.在小数位,则表示转换为对应字符,如果没有则以0表示;在整数位,没有对应则不填充字符. |
6 | B(空格符) | 'B999' | 没有其它特别作用,在整数部分最前面加一个空格,可以出现在任意位置. |
7 | C(国际货币符号) | 'C9999' | 在特定的位置返回一个ISO货币符号(就是NLS_ISO_CURRENCY参数所代表的值) |
8 | D(ISO小数位符号) | '999D99' | 这是"点号"的国际版本(ISO),作用等同于点号,也是只能出现一次.所不同的是,数据库会根据NLS_NUMERIC_CHARACTER的参数值来设置内容.默认的这个值是点号. |
9 | EEEE(科学计算符) | 9.9EEEE | 科学计算符号 |
10 | G(分组符号) | 999G999 | 是逗号(,)的的ISO标准,作为分组符号使用,可以放在多个地方使用. |
11 | L(本地货币符号) | 'L999' | 是C的本地版本.可以放在整个格式的最前面和最后面. |
12 | MI(负号) | '9999MI' | 如果是负数,在尾部加上负号(-),如果是正数,则尾巴加上空格 |
13 | PR(符号) | 9999PR | 是表达负数的另外一种方式.如果是正数,则头部加上空格;如果是负数,则用小简括号<>把数字包起来. |
14 | RN(rn) | RN(rn) | 把整数(1-3999)转换为罗马字符.RN表示转为大写,rn表示小写的. |
15 | S | '9999S' | 是12,13的综合改进版本.为整数加一个正号+,为负数加一个符号-.S在前则加在前,在后则在后. |
16 | TM | TM9/TMe | 使用这个参数等于没有用参数to_char(number)一样,应为'tm9'是默认的格式参数. |
17 | U | U999 | 双币符号,例如欧元.作用同11的L |
18 | V | 999V9 | 这是个比较古怪,又不是很常使用的符号。它的作用在于做一个计算。 |
19 | X | xxxx | 转换为16进制。 |
20 |
|
| 通过以上的例子,我们了解了各种数字的格式。可以说格式太多样,难于记在脑子,最好是作为一个参考存在着. |
1.2 格式说明
从上图可以看到格式是可选取的,保留字fmt也不是必须的,关键是NLSPARAM的意思,
从第2-54章节(FORMAT MODELS)查看。
NLSPARAM可以是这样 'NLS_NUMERIC_CHARACTERS= ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = territory '
举例:(待续)
SELECT TO_CHAR(-10000,'C99G999D99PR',
'NLS_NUMERIC_CHARACTERS=''._'' NLS_ISO_CURRENCY=''UNITED KINGDOM''')"Amount"
FROM DUAL;
结果返回:<GBP10_000.00>
如果要了解可以使用的NLS_ISO_CURRENCY值,可以查询数据库的视图V_$NLS_VALID_VALUES
二, TO_CHAR(CHARACTER)
把NCLOB,CLOB,NCHAR转换为VARCHAR2.
三, TO_CHAR(DATETIME)
把日期转化为字符串.
关于这个格式,没有什么太好说的。它的格式主要分为两类:简写单个字母(或者其复现形式)代表时间位置譬如yyyy ,mm,dd ,hh之类;其次是以英文的时间单词的简写代表时间,例如mon,day,year.
下表的格式基本上也都可以用于TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL,
TO_DSINTERVAL函数。
序号 | 格式 | 简例 | 说明 |
1 | - / , . ; : | 略 | 时间分隔符号,除了标准的几个,还允许用文字作为分割符号。 |
2 | AD |
| 即拉丁文Anno Domini的简写,表示公元.会根据nls的不同转换为公元或者ad等 |
3 | AM |
| 上午的简写 ,同pm, p.m. (下午) , 中文环境输出为上午(如果是上午) |
4 | BC |
| 虽然标准的写法是B.c. (c小写)或者BC,好在Oracle不讲究这个。表示公元前 |
5 | CC |
| 返回世纪,以阿拉伯数字表示 |
6 | D |
| 一周之中的某天,返回的是序号1-7 |
7 | DAY |
| 一周之中的某天,不过返回的是星期几而已,这和语言设置有关系,在中国环境 NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE ,用星期一到星期天表示 |
8 | DD |
| 月份中的某天(1-31) |
9 | DDD |
| 年份中的某天(1-366) |
10 | DL | 'DL' | 返回长的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。例 2008年4月28日星期一 |
11 | DS |
| 返回短的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。例如 2008-04-28 |
12 | DY |
| 日期的简称,就是星期几(当然这指的是中国环境下) |
13 | E |
| 纪元简称,但是只适合以下集中日历:日本皇室,中华民国,太过佛历 |
14 | EE |
| 纪元全程,适合情况同E |
15 | FF [1..9] |
| 就是毫秒,如果不更上数字就是用默认的精度。 |
16 | FM |
| 值得注意的一个函数:不返回任何内容。 |
17 | FX |
| 同上 |
18 | HH |
| 表示小时,为12小时制,同hh12(1-12) |
19 | HH12 |
| 表示小时,为12小时制(1-12) |
20 | HH24 |
| 表示小时,为24小时制(0-23) |
21 | IW |
| ISO标准的星期序号(1-52,或者1-53) |
22 | IYYY |
| IYY,IY,I, ISO年(4位)的4,3,2,1位数字(倒数) |
23 | J |
| 儒略日(多用于天文的一种日历),从公元前4712年一月一日算起,得出的结果是个整数,算法大体为(公元日期+4712)*儒略日历年平均天数 |
24 | MI |
| 秒(0-59) |
25 | MM |
| 2位月(1-12) |
26 | MON |
| 月的简称,和国家有关系NLS_DATE_LANGUAGE,例如04在中文环境下用4月表示. |
27 | MONTH |
| 月的名称,国家有关系NLS_DATE_LANGUAGE,目前在中文下04表示为4月。 |
28 | PM |
| 同am,a.m.表示下午 |
29 | Q |
| 季度(1-4) |
30 | RM |
| 用罗马数字表示的月份,I ,II ,III ,IV ,V ,VI ,VII ,VIII,IX ,X ,XI ,XII |
31 | RR |
| 有点四舍五入表示年的意思,具体的用法有那么一点点复杂。 |
32 | RRRR |
| 如果输入参数只有两位,则同rr,否则就同yyyy作用. |
33 | SS |
| 秒(0-59),一分钟内 |
34 | SSSSS |
| 一天从午夜开始的累积秒数.(0-86399) |
35 | TS |
| 返回短日期格式内容,包括时分秒等,只能和dl,ds组合使用,格式是: |
36 | TZD |
| 夏令时制信息,时区简写加上夏令时信息,必须和格式tzr设置的时区对应。 |
37 | TZH |
| 时区中的小时,例如hh:mi:ss.fftzh:tzm' |
38 | TZM |
| 时区中的分钟. |
39 | TZR |
| 时区中的区域信息,必须是数据库支持的时区,例如US/Pacific |
40 | WW |
| 和iw类似,也是表示星期的序号,从年的第一天算起到年的最后一个第七天。二者取值基本相同。(1-53),例如2008-01-01 到2008-01-07 算1,2008-01-09~2008-01-13 算2 |
41 | W |
| 一个月中的星期序号,其算法同ww,不过是局限在一月之内而已,和iso的不同。 |
42 | X |
| 代表本地根符号,没有特别用处,只能和timestamp类型一起使用. |
43 | Y,YYY |
| 四位年,用都好分隔例如2,008 |
44 | YEAR |
| 发音表达的年,例如 2008=two thousand eight |
45 | YYYY |
| 四位年,S前缀表示公元前BC |
46 | YYY |
| 一次表示后面3,2,1位的年,例如2008 可以分别取值为008,08,8 |
| 总结 |
| 从以上看,主要就是表示时间几个部分的格式:世纪、年,月,日,时,分,秒,毫秒,以及其它一些混合格式。每个时间部分都可以有多种的表达方式,通过这样归类就比较容易记忆。 |
使用前对一些不确定的先试验一下最好,实在不行就查阅oracle的原始文档. |
由于可以采取非常多的格式,所以实在是很灵活的.下面简单的举例几个:
SQL> selectto_char(sysdate,' PM yyyy-mm-dd hh24:mi:sssss AD year mon day dddiw') FROM DUAL;
TO_CHAR(SYSDATE,'PMYYYY-MM-DDH
--------------------------------------------------------------------------------
上午 2008-03-27 09:58:35917公元 twothousand eight 3月 星期四 087 13
SQL> SELECTTO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF5') FROM DUAL;
TO_CHAR(SYSTIMESTAMP,'HH24:MI:
------------------------------
10:02:28.90000
SQL>SELECT TO_CHAR(SYSDATE,'DS DL') FROM DUAL
TO_CHAR(SYSDATE,'DSDL')
-----------------------------------
2008-03-272008年3月27日星期四
最后一个和国家地区有关.