§ Oracle兼容-函数-TO_CHAR()函数


§ 1. 语法

TO_CHAR( number  [, fmt [, string ] ] )
TO_CHAR( datetime  [, fmt [, string ] ] )
1
2

§ 2. 定义和用法

TO_CHAR() 函数的作用是将数值型(number)或者日期型(datetime)转化为字符型(varchar)。

执行 TO_CHAR() 函数时,如果不带fmt 参数则等价于 CAST() 转换的结果,与在Oracle中的结果不一致。

TO_CHAR() 函数转换数据类型的几条原则是:

  1. 将时间类型格式化为字符串。
  2. 将数值类型转换成字符串。
  3. 参数3(string)为了兼容Oracle字符集参数用法,实际不生效,也不进行校验,视为保留参数。

当前支持时间类型格式符号:

格式符号 说明 与Oracle 差异
!@#$%^*()_+-{}:<>?[]\;,.'\`=&\| 复制标点符号
“text” 复制引用文本
AD / A.D. 带或不带句点的 AD
AM / A.M. 带上午指示
BC / B.C. 公元
CC 世纪,例如:2002 返回 21; 2000 返回 20
D 星期几 1-7(默认星期天作为1) 可以根据 default_week_format 定义星期一或星期天作为一周的第一天
DAY 日名称
DD 月里面几号 1-31
DDD 年里面的几天(1-366)
DY 日的缩写名称
FF[1-9] 小数秒, 默认保留6 位 也支持sysdate 类型使用
FM 移除导前,或者尾随空格
HH / H12 小时 1-12
HH24 小时 0-23
IW 由ISO 8601标准定义的日历周(1-52或1-53)
I/IY/IYY ISO 8601标准定义的包含日历周的年份的最后3位、2位或1位
IYYY ISO 8601标准定义的包含日历周的年份的4位数字年份
J 儒略日 不支持小于0的年份
MI 分钟 0-59
MM 月份 0-12
MON 月份缩写
MONTH 月份全称 中文:一月, oracle: 1月
PM / P.M. 下午
Q 季度
RM 罗马数字月份
RR 两位数在 21 世纪 20 世纪的日期
RRRR 完整年分
SS 秒 0-59
SSSSS 00:00:00 过后的秒数 (0-86399)
TH 序数后缀 非数字输出除外,其他元素都可以接TH后缀
W 一年中的第几周 (1-53),其中第 1 周从一年的第一天开始
WW 每月的第几周 (1-5),其中第 1 周从每月的第一天开始
X 小数点,与 FF 格式配合使用,如:'HH:MI:SSXFF'
YYYY/YYY / YY / Y
Y,YYY 带有逗号的年份
YEAR 年份的英文拼写 不支持小于0的年份

备注:

  • 由于 | 还是Oracle 的连接符,因此当单个 | 出现时会被忽略。
  • 在日期时间格式串中,当单个 | 出现时会被忽略,出现才多个时才不会被忽略。
  • 单个 \(反斜线,不含双引号)会被认为是转义符,需要显示 \ 的时候需要写成 \\
  • 当非数字输出的格式,例如:DY/DAY/AD/BC/AM/PM/MON/MONTH/YEAR/RM 等,后面跟随 TH 后缀时,会产生错误提示:ERROR 3047 (HY000): Invalid argument error: ... TH in function to_char
  • 当使用 CC 格式时,如果年份小于0,或者大于9900时,会产生错误提示:ERROR 3048 (HY000): Out of range error: year must be between 0001 and 9900 in function to_char

示例:

-- 单个 | 出现时,会被忽略
-- 多于一个 | 出现时,才不会被忽略
greatsql> SELECT TO_CHAR(NOW(), 'YYYY|MM|DD'), TO_CHAR(NOW(), 'YYYY|MM||DD||');
+------------------------------+---------------------------------+
| TO_CHAR(NOW(), 'YYYY|MM|DD') | TO_CHAR(NOW(), 'YYYY|MM||DD||') |
+------------------------------+---------------------------------+
| 20230511                     | 202305|11|                      |
+------------------------------+---------------------------------+

greatsql> SELECT TO_CHAR("GreatSQ|L数据库"), TO_CHAR("GreatSQL\数据库"), TO_CHAR("GreatSQL\\数据库");
+-------------------------------+-------------------------------+--------------------------------+
| TO_CHAR("GreatSQ|L数据库")    | TO_CHAR("GreatSQL\数据库")    | TO_CHAR("GreatSQL\\数据库")    |
+-------------------------------+-------------------------------+--------------------------------+
| GreatSQ|L数据库               | GreatSQL数据库                | GreatSQL\数据库                |
+-------------------------------+-------------------------------+--------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

选项 default_week_format 用于定义星期一或星期天作为一周的第一天:

Mode First day of week
0 Sunday
1 Monday
2 Sunday
3 Monday
4 Sunday
5 Monday
6 Sunday
7 Monday

当前支持数值类型格式符号:

格式符号 说明 限制
$ 美元符号
9 返回具有指定位数的值,如果是正数则带有前导空格,如果是负数则带有前导减号。前导零是空白,除了零值,它为定点数的整数部分返回零。
0 前导零, 尾随0
, (COMMA) 分割符号 在数字格式模型中,组分隔符不能出现在小数字符或句点的右侧。
. 小数点 一个数字格式模型中只能指定一个句点
B 如果为0 返回空字符串, 长度为格式字符串的长度
D 根据local 配置的小数点符号 , 同一个格式字符串只能有一个小数点 一个数字格式模型中只能指定一个句点
G 分割符 在数字格式模型中,组分隔符不能出现在小数字符或句点的右侧。
EEEE 科学计数法
PR 1.负数 使用 <> 2.正数返回带有导前符号的与尾随空格
MI 返回带有尾随减号 (-) 的负值, 返回带有尾随空白的正值。 格式元素只能出现在数字格式模型的最后位置
S 1.返回带有前导减号 (-) 的负值。
2.返回带前导加号 (+) 的正值。
3.返回带有尾随减号 (-) 的负值。
4.返回带有尾随加号 (+) 的正值。
只能出现在数字格式模型的第一个或最后一个位置。 号
TM 返回数值字符串, 默认为 ‘TM9’ 返回数值字符串,‘TME’ 返回科学计数法字符串 1. 不能在此元素之前添加任何其他元素
2.只能在此元素后使用一个 9 或一个 E(或 e),但不能使用它们的任何组合
V 返回乘以 10^N 的值 , n 为 V 后面9 的个数 不能和科学计数法合用
X/x 返回16进制大写/小写,只能是正整数 1.您只能在此元素之前添加 0(返回前导零)或 FM。任何其他元素都会返回错误。如果您既不指定 0 也不指定 FM 与 X,则返回总是有一个前导空格
2.此元素仅接受正值或 0。负值返回错误
FM 移除导前空格字符,与尾随 0

数值的整数部分,如果超过格式化范围将以 '#' 替换返回小数部分,超过格式范围将 四舍五入最后一位

数值部分最大长度不可以超过 64 位,例如:1e+64。

§ 3. 示例


greatsql> SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS' );
+------------------------------------------+
| TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS' ) |
+------------------------------------------+
| 2023/05/11 16:42:52                      |
+------------------------------------------+

greatsql> SELECT TO_CHAR(NOW(), 'YY/MON/DAY HH24:MI:SS' );
+------------------------------------------+
| TO_CHAR(NOW(), 'YY/MON/DAY HH24:MI:SS' ) |
+------------------------------------------+
| 23/MAY/THURSDAY  16:43:10                |
+------------------------------------------+

greatsql> SELECT TO_CHAR(NOW(), 'RR/MONTH/DY HH24:MI:SS' );
+-------------------------------------------+
| TO_CHAR(NOW(), 'YY/MONTH/DY HH24:MI:SS' ) |
+-------------------------------------------+
| 23/MAY      /THU 16:43:23                 |
+-------------------------------------------+

greatsql> SELECT TO_CHAR(0,'99.99') FROM DUAL;
+--------------------+
| TO_CHAR(0,'99.99') |
+--------------------+
| .00                |
+--------------------+

greatsql> SELECT TO_CHAR(0,'090V99' ) FROM DUAL;
+----------------------+
| TO_CHAR(0,'090V99' ) |
+----------------------+
| 00000                |
+----------------------+

greatsql> SELECT TO_CHAR(0.1 ,'90.99' ) FROM DUAL;
+------------------------+
| TO_CHAR(0.1 ,'90.99' ) |
+------------------------+
| 0.10                   |
+------------------------+

greatsql> SELECT TO_CHAR(-0.2 ,'90.99' ) FROM DUAL;
+-------------------------+
| TO_CHAR(-0.2 ,'90.99' ) |
+-------------------------+
| -0.20                   |
+-------------------------+

greatsql> SELECT TO_CHAR(1 ,'B9999' ) FROM DUAL;
+----------------------+
| TO_CHAR(1 ,'B9999' ) |
+----------------------+
| 1                    |
+----------------------+

greatsql> SELECT TO_CHAR(0 ,'B90.99' ) , LENGTH(TO_CHAR(0 ,'B90.99' )) FROM DUAL;
+-----------------------+-------------------------------+
| TO_CHAR(0 ,'B90.99' ) | LENGTH(TO_CHAR(0 ,'B90.99' )) |
+-----------------------+-------------------------------+
|                       |                             6 |
+-----------------------+-------------------------------+

greatsql> SELECT TO_CHAR(123.456 ,'999.999' ) FROM DUAL;
+------------------------------+
| TO_CHAR(123.456 ,'999.999' ) |
+------------------------------+
| 123.456                      |
+------------------------------+

greatsql> SELECT TO_CHAR(-123.456 ,'999.999' ) FROM DUAL;
+-------------------------------+
| TO_CHAR(-123.456 ,'999.999' ) |
+-------------------------------+
| -123.456                      |
+-------------------------------+

greatsql> SELECT TO_CHAR(123.456 ,'FM999.009' ) FROM DUAL;
+--------------------------------+
| TO_CHAR(123.456 ,'FM999.009' ) |
+--------------------------------+
| 123.456                        |
+--------------------------------+

greatsql> SELECT TO_CHAR(123.456 ,'9.9EEEE' ) FROM DUAL;
+------------------------------+
| TO_CHAR(123.456 ,'9.9EEEE' ) |
+------------------------------+
| 1.2e+02                      |
+------------------------------+

greatsql> SELECT TO_CHAR(1E+123 ,'9.9EEEE' ) FROM DUAL;
ERROR 3049 (HY000): Overflow error: number in function to_char.

greatsql> SELECT TO_CHAR(NOW(),'9.9');
ERROR 3047 (HY000): Invalid argument error: 9.9 in function to_char.

greatsql> SELECT TO_CHAR(99,'YYY');
ERROR 3047 (HY000): Invalid argument error: YYY in function to_char.

greatsql> SELECT TO_CHAR(1234567890 ,'9999999999S' ) FROM DUAL;
+-------------------------------------+
| TO_CHAR(1234567890 ,'9999999999S' ) |
+-------------------------------------+
| 1234567890+                         |
+-------------------------------------+

greatsql> SELECT TO_CHAR(111,'99');
+-------------------+
| TO_CHAR(111,'99') |
+-------------------+
| ###               |
+-------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

扫码关注微信公众号

greatsql-wx