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

1. 语法

  1. TO_CHAR( number  [, fmt [, string ] ] )
  2. 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/IYYISO 8601标准定义的包含日历周的年份的最后3位、2位或1位
IYYYISO 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
SSSSS00: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。

示例：

  1. -- 单个 | 出现时，会被忽略
  2. -- 多于一个 | 出现时，才不会被忽略
  3. greatsql> SELECT TO_CHAR(NOW(), 'YYYY|MM|DD'), TO_CHAR(NOW(), 'YYYY|MM||DD||');
  4. +------------------------------+---------------------------------+
  5. | TO_CHAR(NOW(), 'YYYY|MM|DD') | TO_CHAR(NOW(), 'YYYY|MM||DD||') |
  6. +------------------------------+---------------------------------+
  7. | 20230511                     | 202305|11|                      |
  8. +------------------------------+---------------------------------+
  10. greatsql> SELECT TO_CHAR("GreatSQ|L数据库"), TO_CHAR("GreatSQL\数据库"), TO_CHAR("GreatSQL\\数据库");
  11. +-------------------------------+-------------------------------+--------------------------------+
  12. | TO_CHAR("GreatSQ|L数据库")    | TO_CHAR("GreatSQL\数据库")    | TO_CHAR("GreatSQL\\数据库")    |
  13. +-------------------------------+-------------------------------+--------------------------------+
  14. | GreatSQ|L数据库               | GreatSQL数据库                | GreatSQL\数据库                |
  15. +-------------------------------+-------------------------------+--------------------------------+

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

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

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

格式符号说明限制
$美元符号
9返回具有指定位数的值，如果是正数则带有前导空格，如果是负数则带有前导减号。前导零是空白，除了零值，它为定点数的整数部分返回零。
0前导零， 尾随0
, (COMMA)分割符号在数字格式模型中，组分隔符不能出现在小数字符或句点的右侧。
.小数点一个数字格式模型中只能指定一个句点
B如果为0 返回空字符串， 长度为格式字符串的长度
D根据local 配置的小数点符号 ， 同一个格式字符串只能有一个小数点一个数字格式模型中只能指定一个句点
G分割符在数字格式模型中，组分隔符不能出现在小数字符或句点的右侧。
EEEE科学计数法
PR1.负数 使用 <> 2.正数返回带有导前符号的与尾随空格
MI返回带有尾随减号 (-) 的负值, 返回带有尾随空白的正值。格式元素只能出现在数字格式模型的最后位置
S1.返回带有前导减号 (-) 的负值。
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. 示例

  2. greatsql> SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS' );
  3. +------------------------------------------+
  4. | TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS' ) |
  5. +------------------------------------------+
  6. | 2023/05/11 16:42:52                      |
  7. +------------------------------------------+
  9. greatsql> SELECT TO_CHAR(NOW(), 'YY/MON/DAY HH24:MI:SS' );
  10. +------------------------------------------+
  11. | TO_CHAR(NOW(), 'YY/MON/DAY HH24:MI:SS' ) |
  12. +------------------------------------------+
  13. | 23/MAY/THURSDAY  16:43:10                |
  14. +------------------------------------------+
  16. greatsql> SELECT TO_CHAR(NOW(), 'RR/MONTH/DY HH24:MI:SS' );
  17. +-------------------------------------------+
  18. | TO_CHAR(NOW(), 'YY/MONTH/DY HH24:MI:SS' ) |
  19. +-------------------------------------------+
  20. | 23/MAY      /THU 16:43:23                 |
  21. +-------------------------------------------+
  23. greatsql> SELECT TO_CHAR(0,'99.99') FROM DUAL;
  24. +--------------------+
  25. | TO_CHAR(0,'99.99') |
  26. +--------------------+
  27. | .00                |
  28. +--------------------+
  30. greatsql> SELECT TO_CHAR(0,'090V99' ) FROM DUAL;
  31. +----------------------+
  32. | TO_CHAR(0,'090V99' ) |
  33. +----------------------+
  34. | 00000                |
  35. +----------------------+
  37. greatsql> SELECT TO_CHAR(0.1 ,'90.99' ) FROM DUAL;
  38. +------------------------+
  39. | TO_CHAR(0.1 ,'90.99' ) |
  40. +------------------------+
  41. | 0.10                   |
  42. +------------------------+
  44. greatsql> SELECT TO_CHAR(-0.2 ,'90.99' ) FROM DUAL;
  45. +-------------------------+
  46. | TO_CHAR(-0.2 ,'90.99' ) |
  47. +-------------------------+
  48. | -0.20                   |
  49. +-------------------------+
  51. greatsql> SELECT TO_CHAR(1 ,'B9999' ) FROM DUAL;
  52. +----------------------+
  53. | TO_CHAR(1 ,'B9999' ) |
  54. +----------------------+
  55. | 1                    |
  56. +----------------------+
  58. greatsql> SELECT TO_CHAR(0 ,'B90.99' ) , LENGTH(TO_CHAR(0 ,'B90.99' )) FROM DUAL;
  59. +-----------------------+-------------------------------+
  60. | TO_CHAR(0 ,'B90.99' ) | LENGTH(TO_CHAR(0 ,'B90.99' )) |
  61. +-----------------------+-------------------------------+
  62. |                       |                             6 |
  63. +-----------------------+-------------------------------+
  65. greatsql> SELECT TO_CHAR(123.456 ,'999.999' ) FROM DUAL;
  66. +------------------------------+
  67. | TO_CHAR(123.456 ,'999.999' ) |
  68. +------------------------------+
  69. | 123.456                      |
  70. +------------------------------+
  72. greatsql> SELECT TO_CHAR(-123.456 ,'999.999' ) FROM DUAL;
  73. +-------------------------------+
  74. | TO_CHAR(-123.456 ,'999.999' ) |
  75. +-------------------------------+
  76. | -123.456                      |
  77. +-------------------------------+
  79. greatsql> SELECT TO_CHAR(123.456 ,'FM999.009' ) FROM DUAL;
  80. +--------------------------------+
  81. | TO_CHAR(123.456 ,'FM999.009' ) |
  82. +--------------------------------+
  83. | 123.456                        |
  84. +--------------------------------+
  86. greatsql> SELECT TO_CHAR(123.456 ,'9.9EEEE' ) FROM DUAL;
  87. +------------------------------+
  88. | TO_CHAR(123.456 ,'9.9EEEE' ) |
  89. +------------------------------+
  90. | 1.2e+02                      |
  91. +------------------------------+
  93. greatsql> SELECT TO_CHAR(1E+123 ,'9.9EEEE' ) FROM DUAL;
  94. ERROR 3049 (HY000): Overflow error: number in function to_char.
  96. greatsql> SELECT TO_CHAR(NOW(),'9.9');
  97. ERROR 3047 (HY000): Invalid argument error: 9.9 in function to_char.
  99. greatsql> SELECT TO_CHAR(99,'YYY');
  100. ERROR 3047 (HY000): Invalid argument error: YYY in function to_char.
  102. greatsql> SELECT TO_CHAR(1234567890 ,'9999999999S' ) FROM DUAL;
  103. +-------------------------------------+
  104. | TO_CHAR(1234567890 ,'9999999999S' ) |
  105. +-------------------------------------+
  106. | 1234567890+                         |
  107. +-------------------------------------+
  109. greatsql> SELECT TO_CHAR(111,'99');
  110. +-------------------+
  111. | TO_CHAR(111,'99') |
  112. +-------------------+
  113. | ###               |
  114. +-------------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx