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

---

1. 语法

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

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\数据库                |
+-------------------------------+-------------------------------+--------------------------------+

选项 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') |
+-------------------+
| ###               |
+-------------------+

扫码关注微信公众号