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

1. 语法

1. TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )

2. 定义和用法

TO_NUMBER 将 expr 转换成一个由 fmt 指定格式的 number 类型的值。

当前支持用法：

1. -- 最简语法
2. TO_NUMBER(expr)

4. -- 自动去除左右空格
5. TO_NUMBER(' xxxxx ')

7. -- 目前fmt参数仅支持常用如"9990.9909"或'9990.9909'格式，整数位以及小数位支持9或0表示格式，不进行四舍五入操作
8. TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )

10. -- 以下两个SQL都返回NULL
11. TO_NUMBER('')
12. TO_NUMBER(null)

14. -- 完整语法
15. TO_NUMBER(expr [ , fmt [ , 'nlsparam'] ] )

参数 fmt 可选值如下表所示：

fmt序号 格式符 含义 举例
1 9 返回具有指定位数的值，如果是正数则带有前导空格，如果是负数则带有前导减号。前导零是空白，除了零值，它为定点数的整数部分返回零 TO_NUMBER(‘1234’,’9999’) 返回 1234
2 0 前导零， 尾随0 TO_NUMBER(‘0234’,’0999’) 返回 234；参数整数部分必须4位，待转换字符串高位以0补充
3 逗号, 字符串中含逗号的转换，格式模型中可指定多个逗号，格式限制为：逗号不能在开端；逗号不能出现在小数点右边 TO_NUMBER(‘1,234.567’,’9,999.999’) 返回 1234.567
4 小数. 小数格式转换, 它是指定小数点的位置 TO_NUMBER(‘0.12’,’0.99’) 返回 0.12
5 EEEE 返回科学计数算法的值(当为‘+’，指数<15,结果显示数字，>=15以后结果显示科学计算数据)；当为‘-’，指数<16,结果显示数字，>=16以后结果显示科学计算数据) TO_NUMBER(‘1.6E+02’,’9.9EEEE’) 返回 160；TO_NUMBER(‘1.6e+16’,’9.9EEEE’) 返回 1.6e16
6 X 十六进制转换为10进制的数 TO_NUMBER(‘4D2’,’XXX’) 返回 1234

3. Oracle兼容说明

  1. 目前支持的 fmt 格式列在上方表格中，其它暂时未支持的格式会触发报错提示。
  2. 科学计数法(EEEE, fmt = 5)格式输出结果与Oracle有差异，执行SQL语句 SELECT TO_NUMBER('1.666e+30','9.999EEEE') FROM DUAL; 在Oracle 得到的结果为 1.666e+30，GreatSQL得到的结果为：1.666e30。
  3. 当设定为 X 格式（fmt = 6），且输入参数值包含特殊字符如 ‘.’ 时，TO_NUMBER(',0.','xxx') 结果与Oracle有差异，Oracle返回一个很大的数值，而GreatSQL判定为fmt格式输入不匹配发出报错。
  4. 执行例如 TO_NUMBER(',123,','99,999,') 时，结果与Oracle有差异，Oracle中会报错，GreatSQL不会报错。目前与已知兼容场景冲突，如：TO_NUMBER(',123','9,9,9,999') 及 TO_NUMBER('123,','9,9,9,999,')，在GreatSQL中都不会报错，这几个例子在GreatSQL中都是返回 123。
  5. 转义符号 \ 在GreatSQL中会被统一过滤处理，与Oracle有差异。只有当设置 sql_mode = NO_BACKSLASH_ESCAPES 时，GreatSQL不会过滤转义字符，这时就与Oracle行为一致了。例如：SET sql_mode = NO_BACKSLASH_ESCAPES; SELECT TO_NUMBER('-\,0.0','9,9.9') FROM DUAL;，会提示错误，与Oracle行为一致。

4. 示例

1. greatsql> SELECT TO_NUMBER('1234.56');
2. +----------------------+
3. | TO_NUMBER('1234.56') |
4. +----------------------+
5. |              1234.56 |
6. +----------------------+

8. greatsql> SHOW CREATE TABLE t1\G
9. *************************** 1. row ***************************
10. Table: t1
11. Create Table: CREATE TABLE `t1` (
12. `id` int unsigned NOT NULL AUTO_INCREMENT,
13. `c1` int unsigned DEFAULT NULL,
14. `c2` varchar(10) NOT NULL,
15. PRIMARY KEY (`id`)
16. ) ENGINE=InnoDB;

18. greatsql> SELECT * FROM t1;
19. +----+------+----------+
20. | id | c1   | c2       |
21. +----+------+----------+
22. |  1 |  211 | 1234abca |
23. |  2 | NULL | 1234     |
24. +----+------+----------+

26. greatsql> SELECT id, c2, TO_NUMBER(c2) FROM t1 WHERE id = 2;
27. +----+------+---------------+
28. | id | c2   | TO_NUMBER(c2) |
29. +----+------+---------------+
30. |  2 | 1234 |          1234 |
31. +----+------+---------------+

33. greatsql> SELECT id, c2, TO_NUMBER(c2, "999999.999") FROM t1 WHERE id = 2;
34. +----+------+-----------------------------+
35. | id | c2   | TO_NUMBER(c2, "999999.999") |
36. +----+------+-----------------------------+
37. |  2 | 1234 |                    1234.000 |
38. +----+------+-----------------------------+

40. -- 输入值不符合要求，超出转换范围
41. greatsql>  SELECT id, c2, TO_NUMBER(c2, "999999") FROM t1 WHERE id = 1;
42. ERROR 1690 (22003): NUMBER value is out of range in 'to_number'

44. -- fmt参数格式错误
45. greatsql> SELECT TO_NUMBER(123.456, 'a999.99') ;
46. ERROR 1525 (HY000): Incorrect format model value: 'a999.99'

48. -- fmt参数格式正确，但来源数据不符合格式
49. greatsql> SELECT TO_NUMBER(123333.456, '999.99') ;
50. ERROR 1690 (22003): NUMBER value is out of range in 'to_number'

52. -- fmt格式参数超出范围
53. greatsql> SELECT TO_NUMBER(12333333333333333333333333333333333333333.456,'9999999999999999999999999999999999999999999999999999999999999999999999.99') ;
54. ERROR 1059 (42000): Identifier name '9999999999999999999999999999999999999999999999999999999999999999999999.99' is too long

56. greatsql> SELECT TO_NUMBER('12,12,12','99,99,99.99') FROM DUAL;
57. +-------------------------------------+
58. | TO_NUMBER('12,12,12','99,99,99.99') |
59. +-------------------------------------+
60. |                              121212 |
61. +-------------------------------------+

63. greatsql> SELECT TO_NUMBER('0.12','0.99') FROM DUAL;
64. +--------------------------+
65. | TO_NUMBER('0.12','0.99') |
66. +--------------------------+
67. |                     0.12 |
68. +--------------------------+

70. greatsql> SELECT TO_NUMBER('1.6E+02','9.9EEEE') FROM DUAL;
71. +--------------------------------+
72. | TO_NUMBER('1.6E+02','9.9EEEE') |
73. +--------------------------------+
74. |                            160 |
75. +--------------------------------+

77. greatsql> SELECT TO_NUMBER('1.6e+16','9.9EEEE') FROM DUAL;
78. +--------------------------------+
79. | TO_NUMBER('1.6e+16','9.9EEEE') |
80. +--------------------------------+
81. |                         1.6e16 |
82. +--------------------------------+

84. greatsql> SELECT TO_NUMBER('f12','XXX') FROM DUAL;
85. +------------------------+
86. | TO_NUMBER('f12','XXX') |
87. +------------------------+
88. |                   3858 |
89. +------------------------+

91. greatsql> SELECT TO_NUMBER('4D2','XXX') FROM DUAL;
92. +------------------------+
93. | TO_NUMBER('4D2','XXX') |
94. +------------------------+
95. |                   1234 |
96. +------------------------+

98. greatsql> SET sql_mode = NO_BACKSLASH_ESCAPES;

100. greatsql> SELECT TO_NUMBER('-\,0.0','9,9.9') FROM DUAL;
101. ERROR 1690 (22003): NUMBER value is out of range in 'to_number'

103. greatsql> SET sql_mode = DEFAULT;

105. greatsql> SELECT TO_NUMBER('-\,0.0','9,9.9') FROM DUAL;
106. +-----------------------------+
107. | TO_NUMBER('-\,0.0','9,9.9') |
108. +-----------------------------+
109. |                           0 |
110. +-----------------------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx